Relationship Between Entities In A Database Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

When an inter relationship between entities in a database are explained by a way of graphical representation, we use an ER-Entity-relationship Diagram. In ER diagrams three different categories of symbols namely boxes, diamonds and ovals are used to represent data. For example, Diamonds represent relationship; ovals indicate attributes and boxes represent entities.

Entity

An entity is something that has a distinct, separate existence, though it need not be a material existence. In particular, abstractions and legal fictions are usually regarded as entities. In general, there is also no presumption that an entity is animate. Entities are used in system developmental models that display communications and internal processing of, say, documents compared to order processing

Entity instance-person, place, object, event, concept (often corresponds to a row in a table)

Entity Type-collection of entities (often corresponds to a table)

ENTITY TYPES

STONG

WEAK

Relational ship

A relationship captures how two or more entities are related to one another. Relationships can be thought of as verbs linking two or more nouns. Relationships are represented as diamonds, connected by lines to each of the entities in the relationship.

Relationship instance-link between entities (corresponds to primary key-foreign key equivalencies in related tables)

Relationship type-category of relationship…link between entity types

Relationship

Identifying Relationship

Attributes

Attributes are drawn as ovals and are connected with a line to exactly one entity or relationship set.

Attributes that describe the entities of an entity type are normally assigned to an Entity type. In other words, the attributes of an entity type determine the Properties it can have.

Attributes are assigned directly to an entity type, taken over from the ABAP/4

Multivalve Attribute

Attribute

Key Attribute

Partial Key Attribute

Composite Attribute

Derived Attributed

To Draw the Entity Relationship Diagram there are four steps to be followed.

Step 1: Identify the entities.

For the list of activities, identify the subject areas you need to maintain information about. These subjects will become entities.

Step 2: Determine all significant interactions.

In this step we have to find out all the important connection of the Entity Relationship Diagram.

Step 3: Analyze the nature of the interactions.

In this stage we have to study all connections and have to do a simple survey to reconfirm the connections.

Step 4: Draw the ERD.

Final step is drawing the ER-Diagram using the collected information.

1.JPG

1.2

When we consider the above diagram of the company database there are four entities. Each entity has attributes.

Employee

Employee's name, Id, Address, Salary, Birth of Date and Gender and each employee has a supervisor to monitor their works. Each employee is assigned to one department.

Department

Each department has a name, Id and Location which is controlled by the department.

Project

Each project has the name, unique number and a location.

Location

Each department has a location and each projects has a location

1.3

IDENTIFY ENTITIES

An entity type, also simply called entity, is similar conceptually to object-orientation's concept of a class - an entity type represents a collection of similar objects.  

For this above scenario there are four entities Project, Department, Employee and Location.

FIND RELATIONSHIPS

In the real world entities have relationships with other entities. According to the scenario below are the relationships between the entities.

Project to Employee

When we consider this relationship, the Employee must have to do a project to enter this system. It is one to one relationship and it is mandatory,

But many employees can work under one project. It is one to many relationships and it is optional.

Project to Department

In this relationship, Department has to do Project. It is one to one relationship and it is mandatory.

But department may have many projects. It is one to many relationships and it is optional.

Project to Location

In this relationship, Project has to do in one Location. It is one to one relationship and it is mandatory.

As well as one Location must have one Project. It is one to one relationships and it is mandatory.

Department to Location

In this relationship, the Department may have several Locations. It is one to one relationships and it is mandatory.

As well as one Location must have a Department it is one to one relationship and it is mandatory.

Employee to Department

In this relationship, one Department may have many Employees. It is one to one relationship and it is mandatory.

As well as one Employee can work in one Department. It is one to one relationship and it is optional.

DRAW ROUGH ERD

Using rectangles for entities and lines for relationships can draw an Entity Relationship Diagram (ERD). The Entity Relationship Diagram editor in Systems Analyst provides a tool for drawing this type of diagram. Since Systems Analyst is an integrated upper CASE tool, one can associate the entities in the diagram with their primary keys, foreign keys and other attributes identified.

FILL IN CARDINALITY

At each end of each connector joining rectangles, we need to place a symbol indicating the minimum and maximum number of instances of the adjacent rectangle there are for one instance of the rectangle at the other end of the relationship line.

The placement of these numbers is often confusing. The first symbol is either 0 to indicate that it is possible for no instances of the entity joining the connector to be related to a given instance of the entity on the other side of the relationship, 1 if at least one instance is necessary or it is omitted if more than one instance is required. For example, more than one employee can be work in a department, but it is possible for no employee to have a particular department.

DEFINE PRIMARY KEYS

For each entity we must find a unique primary key so that instances of that entity can be distinguished from one another. Often a single field or property is a primary key (Eg: Emp ID). Other times the identifier is a set of fields or attributes

IDENTIFY ATTRIBUTES

A data attribute is a characteristic common to all or most instances of a particular entity. In this step we try to identify and name all the attributes essential to the system we are studying without trying to match them to particular entities.

The best way to do this is to study the forms, files and reports currently kept by the users of the system and circle each data item on the paper copy. Cross out those which will not be transferred to the new system, extraneous items such as signatures, and constant information which is the same for all instances of the form (e.g. The company name and address). The remaining circled items should represent the attributes you need. You should always verify these with your system users.

MAP ATTRIBUTES

For each attribute we need to match it with exactly one entity. Often it seems like an attribute should go with more than one entity (e.g. Name). In this case you need to add a modifier to the attribute name to make it unique (e.g. Staff Name, Employee Name, etc.) or determine which entity an attribute "best' describes.

DRAW FULLY-ATTRIBUTED ERD

If you introduced new entities and attributes in step 8, you need to redraw the entity relationship diagram. When you do so, try to rearrange it so no lines cross by putting the entities with the most relationships in the middle. If you use a tool like Systems Architect, redrawing the diagram is relatively easy.

Task 02

2.1

Traditional File system

Traditional file based system is basically a file based system, in which we manually or through computer handle the database such as updating, insertion ,deletion adding new files to database etc.

We either manually or through computer handle database. In other words files are updated, deleted or inserted in a database. This traditional file based system is in use for a very long time.

There are a number of advantages of traditional file system that includes,

External storage is not necessary.

A person with limited technical knowledge can handle database.

Comparatively processing speed is good than Data Base Management System.

Advantages of Traditional File system

No need of external storage.

No need of highly technical person to handle the database.

Processing speed is high as compare to DBMS.

Disadvantages of Traditional File system

Provide less security.

Redundancy is more.

Less integrity.

High complexity in updating of database.

Database Management System

A special data processing system, or part of a data processing system, which aids in the storage, manipulation, reporting, management, and control of data. Shortened as DBMS.

It is a computer software program that is designed as the means of managing all databases that are currently installed on a system hard drive or network.

Different types of Data Base Management System

Categorized by User:

Single User Database: Support Single user at a time.

Multi User Database: Support Multiuser at a time.

Categorized by Location:

Centralized Database: Support data located in single site.

Distributed Database: Supports data distributed across several different files.

Advantages of Database Management system

Reduced data redundancy.

Reduced updating errors and increased consistency.

Greater data integrity and independence from applications programs.

Improved data access to users through use of host and query languages.

Improved data security.

Reduced data entry, storage, and retrieval costs.

Facilitated development of new applications program.

Disadvantages of Database Management system

Database systems are complex, difficult, and time-consuming to design.

Substantial hardware and software start-up costs.

Damage to database affects virtually all applications programs.

Extensive conversion costs in moving form a file-based system to a database system.

Initial training required for all programmers and users.

2.2

Data models

Data model explicitly determines the meaning of data, which in this case is known as structured data (as opposed to unstructured data for example an image binary file or a natural language text, where the meaning has to be leeboard).Typical applications of data model include database models, design of information system, and enabling exchange of data .usually data models are specified in a data modeling language.

A data model can be sometimes referred to as a data structure especially in the context of programming languages data models are often complemented by function models especially in the context of enterprise models.

There are six types of Data Models.

Hierarchical data model

Network data model

Relational data model

Object oriented data model

Post Relational data model

Deductive data model

Hierarchical data model

A hierarchical data model is a data model in which the data is organized into a tree-like structure. The structure allows repeating information using parent/child relationships: each parent can have many children but each child only has one parent. All attributes of a specific record are listed under an entity type.

In a database, an entity type is the equivalent of a table; each individual record is represented as a row and an attribute as a column. Entity types are related to each other using 1: N mapping, also known as one-to-many relationships.

The hierarchical data model organizes data in a tree structure. There is a hierarchy of parent and child data segments. This structure implies that a record can have repeating information, generally in the child data segments. Data in a series of records, which have a set of field values attached to it. It collects all the instances of a specific record together as a record type. These record types are the equivalent of tables in the relational model, and with the individual records being the equivalent of rows. To create links between these record types, the hierarchical model uses Parent Child Relationships. These are a

1: N mapping between record types. This is done by using trees, like set theory used in the relational model, "borrowed" from maths.

For example, an organization might store information about an employee, such as name, employee number, department, salary. The organization might also store information about an employee's children, such as name and date of birth. The employee and children data forms a hierarchy, where the employee data represents the parent segment and the children data represents the child segment. If an employee has three children, then there would be three child segments associated with one employee segment. In a hierarchical database the parent-child relationship is one to many. This restricts a child segment to having only one parent segment. Hierarchical DBMSs were popular from the late 1960s, with the introduction of IBM's Information Management System (IMS) DBMS, through the 1970s.

The hierarchical data model has the following features

Each hierarchical tree can have only one root record type and this record type does not have a parent record type.

The root carts have any number of child record types and each of which can itself be a root of a hierarchical sub tree.

Each child record type can have only one parent record type; thus an M: N. relationship cannot be directly expressed between two record types.

Data in a parent record applies to all its children records.

Sample picture for hierarchical data model.

2.JPG

Figure: 2.1

Network data model

The network model is a database model conceived as a flexible way of representing objects and their relationships. Its distinguishing feature is that the schema, viewed as a graph in which object types are nodes and relationship types are arcs, is not restricted to being a hierarchy or lattice

The network data model was formalized in the late 1960s by the Database Task Group of the Conference on Data System Language (DBTG/CODASYL). Their first report which has been revised a number of times, contained detailed specifications for the network data model (a model conforming to these specifications is also known as the DBTG data model).

The popularity of the network data model coincided with the popularity of the hierarchical data model. Some data were more naturally modeled with more than one parent per child. So, the network model permitted the modeling of many-to-many relationships in data. In 1971, the Conference on Data Systems Languages (CODASYL) formally defined the network model.

The basic data modeling construct in the network model is the set construct. A set consists of an owner record type, a set name, and a member record type. A member record type can have that role in more than one set, hence the multivalent concept is supported. An owner record type can also be a member or owner in another set. The data model is a simple network, and link and intersection record types (called junction records by IDMS) may exist, as well as sets between them.

Implementation of the Network Data Model

The record is a basic unit to represent data in the DBTG network database model. The implementation of the one-to-many relationships of a set is represented by linking the members of a given occurrence of a set to the owner record occurrence. The actual method of linking the member record occurrence to the owner is immaterial to the user of the database; however, for our discussion, we can assume that the set is implemented using a linked list. The list starts at the owner record occurrence and links all the member record occurrences with the pointer in the last member record occurrence leading back to the owner record.

Rows and columns can be logically manipulated by Boolean operators ("and," "or," "not") to determine records that satisfy complex associations.

A bit map can be manipulated equally as well in either a row or column access (all the row records for a common column or all the column records for a common row) and can be easily extended for n-ray relationships).

Sample picture for the Network data model.

1.JPG

Figure: 2.2

Relational data model

(RDBMS - relational database management system) A database based on the relational model developed by E.F.Cod. A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. In such a database the data and relations between them are organized in tables. A table is a collection of records and each record in a table contains the same fields.

Properties of Relational Tables:

Values Are Atomic.

Each Row is Unique.

Column Values Are of the Same Kind.

The Sequence of Columns is Insignificant.

The Sequence of Rows is Insignificant.

Certain fields may be designated as keys, which mean that searches for specific values of that field will use indexing to speed them up. Where fields in two different tables take values from the same set, a join operation can be performed to select related records in the two tables by matching values in those fields. Often, but not always, the fields will have the same name in both tables.

For example, an "orders" table might contain (customer-ID, product-code) pairs and a "products" table might contain (product-code, price) pairs so to calculate a given customer's bill you would sum the prices of all products ordered by that customer by joining on the product-code fields of the two tables. This can be extended to joining multiple tables on multiple fields. Because these relationships are only specified at retrieval time, relational databases are classed as dynamic database management system. The RELATIONAL database model is based on the Relational Algebra.

The differences that arise in the relational approach is in setting up relationships between different tables. This actually makes use of certain mathematical operations on the relation such as projection, union, joins, etc. These operations from relational algebra and relational calculus are discussion in some more details in the second Block of this course. Similarly in order to achieve the organization of the data in terms of tables in a satisfactory manner, a technique called normalization is used.

Example of a Relational Model

Let us see important features of a RDBMS through some examples as shown in before table a relation has the following properties:

Each column contains values about the same attribute, and each table cell value must be simple (a single value).

Each column has a distinct name (attribute name), and the order of columns is immaterial.

Each row is distinct; that is, one row cannot duplicate another row for selected key attribute columns.

Sample picture for the Relational model.

ldm.gif

Figure: 2.3

2.3

COMPONENTS OF DATABASE SYSTEM

A Data Base system is composed of four main components.

Data

Hardware

Software

Users

DATA

It is a most significant component of the database system. Most of the organizations produce, store and process large amount of data. The data acts a bridge between the machine parts like hardware and software and the users which directly access it or access it through some application programs.

There are three different Types of Data.

User Data 

It consists of a table(s) of data called Relation(s) where Column(s) are called fields of attributes and rows are called Records for tables. A Relation must be structured properly.

Metadata 

A description of the structure of the database is known as Metadata. It basically means "data about data". System Tables store the Metadata which includes.

Number of Tables and Table Names

Number of fields and field Names

Primary Key Fields

Application Meta data 

It stores the structure and format of Queries, reports and other applications components. '

HARDWARE

The hardware consists of the secondary storage devices such as magnetic disks (hard disk, zip disk, floppy disks), optical disks (CD-ROM), magnetic tapes etc. on which data is stored together with the Input or output devices (mouse, keyboard, printers), processors, main memory etc. which are used for storing and retrieving the data in a fast and efficient manner. Since database can range from those of a single user with a desktop computer to those on mainframe computers with thousand of users, therefore proper care should be taken for choosing appropriate hardware devices for a required database.

SOFTWARE

The Software part consists of DBMS which acts as a bridge between the user and the database or in other words, software that interacts with the users, application programs, and database and files system of a particular storage media (hard disk, magnetic tapes etc.) to insert, update, delete and retrieve data.

For performing these operations such as insertion, deletion and update we can either use the Query Languages like SQL, QUEL, Gupta SQL or application software such as Visual Basic, Developer etc.

USERS

Users are those persons who need the information from the database to carry out their primary business responsibilities i.e. Personnel, Staff, Clerical, Managers, Executives etc. On the basis of the job and requirements made by them they are provided access to the database totally or partially.

There are four various types of users who can access the database.

Database Administrators (DBA)

Database Designers

End Users

Application Programmers

Importance of DBMS in commercial & non commercials

Database management system (DBMS) is computer software that manages databases. DBMS may use any of a variety of database models, such as the network model or relational model. In large systems, a DBMS allows users and other software to store and retrieve data in a structured way.

A DBMS is a set of software programs that controls the organization, storage, management, and retrieval of data in a database. DBMS are categorized according to their data structures or types. It is a set of prewritten programs that are used to store, update and retrieve a Database. The DBMS accepts requests for data from the application program and instructs the operating system to transfer the appropriate data. When a DBMS is used, information systems can be changed much more easily as the organization's information requirements change. New categories of data can be added to the database without disruption to the existing system.

Organizations may use one kind of DBMS for daily transaction processing and then move the detail onto another computer that uses another DBMS better suited for random inquiries and analysis. Overall systems design decisions are performed by data administrators and systems analysts. Detailed database design is performed by database administrators.

Database servers are specially designed computers that hold the actual databases and run only the DBMS and related software. Database servers are usually multiprocessor computers, with RAID disk arrays used for stable storage. Connected to one or more servers via a high-speed channel, hardware database accelerators are also used in large volume transaction processing environments. DBMSs are found at the heart of most database applications. Sometimes DBMSs are built around a private multitasking kernel with built-in networking support although nowadays these functions are left to the operating system.

Database management system is the system of computer software that is aimed to provide a managing tool for maintaining the data, through various data models. If you look over the large companies or even the smaller ones, can you believe that all of them are maintaining the manual registers for the collection of data? Obviously not! The invention of computer and the software has made it easy to manage the data of all types in the database management system and then having a direct access to data more quickly and easily than the manual system. For example, if you go to your doctor for a routinely check up, he will have a record of the last time you visited him. This shows that for each patient he will have the same record and this is what the database management system is supposed to do.

TASK 03

3.1

NORMALIZATION

Normalization means the process of efficiently organizing data in a database. There are two main goals in this normalization process. One is eliminating redundant data that is the same data which is stored in more than one table should be eliminated. And the other one is ensuring data dependencies make sense that means store only the related data in the table.

These two main goals are worthy and they reduce the amount of space a database consumes and ensure that data is logically stored.

Database normalization can essentially be defined as the practice of optimizing table structures. Optimization is accomplished as a result of a through investigation of the various pieces of data that will be stored within the database, in particular concentrating upon how this data is interrelated. An analysis of this data and its corresponding relationships is advantageous because it can result both in a substantial improvement in the speed in which the tables are queried, and in decreasing the chance that the database integrity could be compromised due to tedious maintenance procedures

The Normal Forms

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (The lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won't be discussed in this article.

Before we begin our discussion of the normal forms, it's important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it's extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies. That said, let's explore the normal forms.

First Normal Form (1NF)

First normal form is a normal form which is used in normalization in a database. It sets the very basic rules for an organized database. It can be also called as Minimal Form. A database table that adheres to 1NF is one that meets a certain minimum set of criteria. These criteria are basically concerned with ensuring that the table is a faithful representation of a relation and that it is free of repeating groups.

There are three steps to be followed in the 1st Normal Form.

Step 1: Eliminating the repeating groups.

It is to present the data in tabular format, where each cell has single value and there are no repeating groups.

Eliminating repeating groups, eliminate nulls by making sure that each repeating group attribute contains an appropriate data value.

Step 2: Identify the Primary key.

Primary key is uniquely identified attribute value.

New key must be composed.

Step 3: Identify all Dependencies.

Dependencies can be depicted with help of a diagram

Dependency diagram depicts all the dependencies found within given table structure. It is used to view all the relationships among table's attributes.

First normal form describes tabular format in which:

All key attributes are defined.

There are no repeating groups in the table.

All attributes are dependent on primary key.

All relational tables satisfy 1NF requirements.

Some tables contain partial dependencies.

Dependencies based on only part of the primary key.

Sometimes used for performance reasons, but should be used with care

Still subject to data redundancies.

Lone No.

Lone Type

Customer No.

Customer Name

Vehicle Type

Model

Year of Manufacture

Loan of Amount

Table 3.1

(Lone No, Loan Type, Customer No, Customer Name, Vehicle Type, Model, Year of Manufacture, Loan Amount)

Table Name: Lone

Lone No

Lone Type

Customer No

Customer Name

Vehicle Type

Model

Year of Manufacture

Loan of Amount

L1

A

001

Kala

3120

Toyota

1990

125,000

L1

A

001

Kala

N1802

Nissan

1985

150,000

L2

B

002

Shiva

3120

Toyota

1990

300,000

L2

B

002

Shiva

6300

Kia

1993

250,000

L3

C

001

Kala

6300

Kia

1993

175,000

Table: 3.2

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data. It meets all the requirements of the first normal form. In this Normal form we remove the subsets of data that apply to multiple rows of a table and place them in separate tables. By the use of foreign keys relationships between these new tables and their predecessors will be created.

There are two steps to be followed in the 2nd Normal Form.

Step 1: Write each key component on a separate line.

Step 2: Assign Corresponding Dependent Attributes.

Determine those attributes that are dependent on other attributes. In this 2nd Normal Form most anomalies (Insertion, Update, Deletion) will be eliminated.

Partial Dependency

(Loan No, Lone Type, Customer No, Customer Name)

Lone No

Lone Type

Customer No

Customer Name

Table: 3.3

Table Name: Customer

Lone No

Lone Type

Customer No

Customer Name

L1

A

001

Kala

L2

B

002

Shiva

L3

C

001

Kala

 

 

 

Table: 3.4

Partial Dependency

(Vehicle Type, Model, Year of Manufacture)

Vehicle Type

Model

Year of Manufacture

Table: 3.5

Table Name: Vehicle

Vehicle Type

Model

Year of Manufacture

3120

Toyota

1990

N1802

Nissan

1985

6300

Kia

1993

Table: 3.6

Composite Key

(Loan No, Vehicle Type, Loan Amount)

Loan No.

Vehicle Type

Loan Amount

Table: 3.7

Table Name: Loan

Loan No

Vehicle Type

Loan Amount

L1

3120

125,000

L1

N1802

150,000

L2

3120

300,000

L2

6300

250,000

L3

6300

175,000

Table: 3.8

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further. It meets all the requirements of the second normal form. In this 3rd Normal form columns that are not dependent upon the primary key will be removed.

There are three steps to be followed in the 3rd Normal Form.

Step 1: Identify new determinant.

Step 2: Identify the dependent attributes.

Step 3: Remove the dependent attributes from transitive Dependencies.

Transitive Dependency

(Loan No, Lone Type, Customer No)

Lone No

Lone Type

Customer No

Customer Name

Table: 3.9

Table Name: Loan

Lone No

Lone Type

Customer No

L1

A

001

L2

B

002

L3

C

001

Table: 3.10

Transitive Dependency

(Customer No, Customer Name)

Customer No

Customer Name

Table: 3.11

Table Name: Customer

Customer No

Customer Name

001

Kala

002

Shiva

Table: 3.12

Transitive Dependency

(Vehicle Type, Model, Year of Manufacture)

Vehicle Type

Model

Year of Manufacture

Table: 3.13

Vehicle Type

Model

Year of Manufacture

3120

Toyota

1990

N1802

Nissan

1985

6300

Kia

1993

Table Name: Vehicle

Table: 3.14

Composite Key

(Loan No, Vehicle Type, Loan Amount)

Loan No.

Vehicle Type

Loan Amount

Table: 3.15

Table Name: Amount

Loan No

Vehicle Type

Loan Amount

L1

3120

125,000

L1

N1802

150,000

L2

3120

300,000

L2

6300

250,000

L3

6300

175,000

Table: 3.16

3.3

1.JPG

Figure: 3.1

2.JPG

Figure: 3.2

3.JPG

Figure: 3.3

4.JPG

Figure: 3.4

5.JPG

Figure: 3.5

6.JPG

Figure: 3.6

7.JPG

Figure: 3.7

8.JPG

Figure: 3.8

9.JPG

Figure: 3.9

TASK 04

4.1

4.1.1

CreateTable Department (

Dept_name Varchar(20),

Dept_location Varchar(20),

Dept_no intPRIMARYKEY,

No_of_employees int,

Dep_supervisor_id Varchar(20));

Figure: 4.1

Figure: 4.2

4.1.2

4.1.2.1

InsertInto Department

(Dept_name,Dept_location,Dept_no,No_of_employees,Dep_supervisor_id)

Values('Finance','South region',01,250,'S1')

Figure: 4.3

Figure: 4.4

InsertInto Department (Dept_name,Dept_location,Dept_no,No_of_employees,Dep_supervisor_id)

Values('HR','Main region',02,200,'S4')

Figure: 4.5

Figure: 4.6

InsertInto Department

(Dept_name,Dept_location,Dept_no,No_of_employees,Dep_supervisor_id)

Values('IT','North building',03,650,'S9')

Figure: 4.7

Figure: 4.8

InsertInto Department

(Dept_name,Dept_location,Dept_no,No_of_employees,Dep_supervisor_id)

Values('Marketing','Main region',04,450,'S13')

Figure: 4.9

Figure: 4.10

4.1.2.2

Select Dept_name, No_of_employees

From Department

Where Dept_location='Main region'

Figure: 4.11

4.1.2.3

UPDATE Department

SET No_Of_Employees=350

WHERE Department_Name='IT'

Figure: 4.12

Figure: 4.13

4.2

MICROSOFT SQL SERVER 2005

SQL Server 2005 introduced "MARS" (Multiple Active Results Sets), a method of allowing usage of database connections for multiple purposes.

SQL Server 2005, released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data. For this purpose, it defined an xml data type that could be used either as a data type in database columns or as literals in queries. XML columns can be associated with XSD schemas; XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database. Specialized indexing methods were made available for XML data. XML data is queried using Query SQL Server 2005 added some extensions to the T-SQL language to allow embedding Query queries in T-SQL. In addition, it also defines a new extension to Query, called XML DML that allows query-based modifications to XML data. SQL Server 2005 also allows a database server to be exposed over web services using TDS packets encapsulated within SOAP (protocol) requests. When the data is accessed over web services, results are returned as XML

For relational data, T-SQL has been augmented with error handling features and support for recursive queries. SQL Server 2005 has also been enhanced with new indexing algorithms and better error recovery systems. Data pages are check summed for better error resiliency, and optimistic concurrency support has been added for better performance. Permissions and access control have been made more granular and the query processor handles concurrent execution of queries in a more efficient way. Partitions on tables and indexes are supported natively, so scaling out a database onto a cluster is easier. SQL CLR was introduced with SQL Server 2005 to let it integrate with the .NET Framework

4.3VB.JPG

Figure: 4.14

Empty.JPG

Figure: 4.15

Vb 123.JPG

Figure: 4.16

Table.JPG

Figure: 4.17

Department name

Finance

Location

South region

No

01

No of employees

250

Supervisor id

S1

Department name

HR

Location

Main region

No

02

No of employees

200

Supervisor id

S4

Department name

IT

Location

North building

No

03

No of employees

650

Supervisor id

S9

Department name

Marketing

Location

Main region

No

04

No of employees

450

Supervisor id

S13

Table: 4.1

CONCLUSION

In this first task of this assignment I have explained the particular tools and techniques, the ERD model, which I have chosen for the given scenario. I have used Microsoft Visio 2007 to draw an ER- Diagram. From this task I came to know what an entity, attributes, relationship is.

In the second task I have explained the traditional file system and database management system, and then I have explained the data models and the components of the database. Finally in this task I have explained the importance of the DBMS in commercial and non commercial environment.

For the third task, I have explained the steps of the Normalization. I have used the modeling tool, Entity relationship diagrams to normalize the given data set. I have designed a fully working database for the given data set. For this fully working database I have used Microsoft SQL server 2005.

For the fourth and final task I have written the SQL codes (Insert, Update, Delete, Select) to produce a table containing the information that given in that task. Then I have explained about the software, Microsoft SQL server 2005. Finally I have developed a sample input and output screens for the above database design.

GANNT CHART

Gannt Chart.JPG

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.