An Entity-Relationship Diagram is a information mold technique that creates a graphical representation of the entities. It is relationships between entities, within an information system. This diagram is often used as a manner to visualise a relational database: each entity is to show a database tabular array and the relationship lines represents the keys in a tabular array that point to specific records in related tabular arraies. Furthermore, ERD may besides be more abstract, its non needfully capturing every tabular array needed within a database, but functioning to plot the major constructs and relationships.

Definition of relationship

A relationship is some association between entities ; it ‘s shown by line between entities. Besides, a relationship line is to demo that each case of an entity may hold a relationship with cases of the affiliated entity, or frailty versa.

Definition of entity property

An entity is characterized by a figure of belongingss or properties. Valuess assigned to properties are used to separate one entity from another.

Definition of Optionality and Cardinality

Symbols are the terminals of the relationship lines to demo the “ Optionality ” and the “ Cardinality ” of each relationship. For the “ Optionality ” , it ‘s to show whether the relationship is optional or compulsory ; and the “ Cardinality ” , it ‘s to show the maximal figure of relationships.

An entity can specify as a thing which an organisation recognizes as being able of an independent being and which can be unambiguously identified. In add-on, an entity is an abstraction from the complexnesss of some sphere. Each entity is shown in box within the ERD.

Relationships mean that in a relational database, all entities have bonds between them, expressed as relationships. The relationship is a nexus between to entities, and it tells about something which relationship exists between our entities. In the ER attack, more than one relationship can be between any two entities.

To see the issue of position integrating, add the field “ Complaint Type ” to associations between two entities of “ Purchases ” and “ Customer Details ” for the information demands of Island Dreams.

Merchandise

Complaint Type

Purchases

Customer Detailss

1.2 Data Dictionary

Data lexicon is the chief ground to hive away dictionary information in the database diagram itself is easiness of informations entry in database direction systems, a file that defines the basic organisation of a database. Its include a list of all files in the database, the figure of records in each file, and the types and name of each field. Most database direction systems maintain the informations dictionary hidden from users to debar them from by chance destructing its contents.

The basic information that is stored in a information lexicon for a relational DBMS includes:

A description of base dealingss, including relation names, column names, column informations types and nothing characteristicsof columns.

Primary-key and foreign cardinal declarations, including extension restraints.

A description of positions.

Declarations of user groups and authoriztions.

Furthemore, informations lexicon is a agency for entering the meta-data of some organisatioin and its do n’t incorporate any existent information from the database, merely bookkeeping information for a managing. If without a information lexicon, a database direction system can non entree informations from the database.

Harmonizing to pattern the information demands of Island Dreams, used the Data Dictionary to hive away and mange definitions of procedures, informations flows and informations shop, to group of related informations elements and other informations constructions.

1.2.1 Customer Detailss

Data Dictionary

Customer Number

00127

Customer Telephone

02098888888

Customer Name

Dave Higgins

Returning Customer

Yes

Address

27 Acacia Avenue Upper Farringdon Rutlandshire England

Notes

Customer diabetic.

1.2.2 Purchases

Data Dictionary

Merchandise Code

STK01/2004

Merchandise Name

Holiday 1 on St Kilda 2004 01-06-04 to 17-06-04

Product Type Code

HOL

Merchandise Type

Vacation Box

Data Dictionary

Merchandise Code

BTH/STK

Merchandise Name

Boat Hire St Kilda

Product Type Code

Bohrium

Merchandise Type

Boat Hire

Data Dictionary

Merchandise Code

FE/STK

Merchandise Name

Fishing Equipment St Kilda

Product Type Code

EH

Merchandise Type

Equipment Hire

Data Dictionary

Merchandise Code

AC/STK

Merchandise Name

All conditions vesture St Kilda

Product Type Code

EH

Merchandise Type

Equipment Hire

Data Dictionary

Merchandise Code

SU01/2004

Merchandise Name

Holiday 1 on South Uist 18-06-04 to 29-06-04

Product Type Code

HOL

Merchandise Type

Vacation Box

Data Dictionary

Merchandise Code

BTH/SU

Merchandise Name

Boat Hire South Uist

Product Type Code

Bohrium

Merchandise Type

Boat Hire

Undertaking 2 – Resulting Tables of the Primary and Foreign Keys

2.1 Normalized Tables

Table is a information ( value ) , which is the theoretical account of the perpendicular columns ( which identifies the name ) and the horizontal lines. A specified figure of columns in the tabular array, but may be any figure of rows. Each row is to place the subset of the values in the column, which has been identified as a campaigner key.

Furthermore, the existent information rows, the panels are by and large associated with some other meta-data, such as limitations on the tabular array or the values in columns.

2.2 Definition of Standardization

Database standardization is a technique to cut down to the minimal duplicate of information for planing relational database tabular arraies and in making so to keep the database for certain types of structural and logical jobs, that is to state informations anomalousnesss. For illustration, when a given piece of information has multiple cases to be found in a tabular array, this possible happen that these instance will non be unbroken consistent during the information is updated in the tabular array, taking to a loss of informations unity. The such sort of jobs are less vulnerable to a sufficiently normalized tabular array, because its construction reflects the basic premises of the multiple cases of the same information it should be used when a individual case of merely.

There are regulations for UNF, 1NF, 2NF, 3NF, BCNF, 4NF, 5NF and domain-Key NF. Most textbooks reference 5NF and DKNF merely in go throughing and note that they are non peculiarly applicable to be design procedure. Standardization is truly about the “ formalism of simple thoughts ” . All excessively frequently, the simpleness is lost in esoteric nomenclature and documents are “ frequently overly concerned with the formalism and supply really practical penetration ” .

In this undertaking, why need a standardization the database, it is because standardization is about planing a “ good ” database i.e. a set of related narratives with a lower limit of excess informations and no update, delete or insert anomalousnesss.

Standardization is a “ underside up ” attack to database design, the interior decorator interviews users and collects paperss – studies etc. The information on a study can be listed and so normalized to bring forth the needed tabular arraies and properties.

2.3 Phase of Normalization

Roll up the data-set – the set of data-items

Transform the normalized data-set into tabular arraies in first normal signifier

Transform foremost normal signifier tabular arraies to back normal signifier

Transform 2nd normal signifier tabular arraies to third normal signifier

Transform 3rd normal signifier to Boyce-Codd normal signifier

Transform 3rd normal signifier to 4th normal signifier

Transform 4th normal signifier to fifth normal signifier

2.4 Primary key:

Primary key is a field or combination of Fieldss that identify a record in the tabular array, so each ticket can be placed without confusion.

Primary key is the field that unambiguously identifies each record. For illustration, the primary key is alone to each record and the value will ne’er be duplicated in the same tabular array. A restraint is a regulation that defines what information is is valid for the country. So the primary cardinal restraint is the regulation says that the primary cardinal field can non be empty and can non incorporate duplicate informations.

Database systems usually have more than one tabular array, and these are normally related in any manner. For illustration, a client tabular array and an order tabular array are to associate to each other on a alone client figure. The client tabular array will ever be a record for each client, and the Order tabular array has a record for each order that the client has.

2.5 Foreign keys:

A foreign key is a key used to associate two tabular arraies together. Typically, it ‘s will hold a primary cardinal field from one tabular array and paste it into another tabular array, which becomes the foreign key ( the primary key in the original tabular array ) .

A foreign cardinal restraint that the informations in the foreign keys must be consistent with the primary key of the tabular array is linked. This is called mention unity is to guarantee that informations entered is right and is non alone

2.6 The Resulting Tables of Bespeaking the Primary and Foreign Keys:

2.6.1 Customer Detailss

CREATETABLE Customer Details

( Customer Number VARCHAR ( 6 ) NOT NULL

Customer Telephone VARCHAR ( 11 ) ,

Customer Name VARCHAR ( 15 ) NOT NULL,

Returning Customer VARCHAR ( 3 ) NOT NULL,

Address VARCHAR ( 50 ) ,

Notes VARCHAR ( 50 ) ,

PRIMARYKEY ( Customer Number ) ,

FOREIGNKEY ( Complaint Type IDENTIFIES Customer Details ) )

2.6.2 Purchases

CREATETABLE Purchases

( Product Code VARCHAR ( 15 ) NOT NULL

Merchandise Name VARCHAR ( 50 ) NOT NULL,

Product Type Code ( 5 ) NOT NULL,

Merchandise Type ( 25 ) ,

PRIMARYKEY ( Product Code ) ,

FOREIGNKEY ( Complaint Type IDENTIFIES Purchases ) )

2.6.3 Complaint Type

CREATETABLE Complaint Type

( Complaint Type VARCHAR ( 3 ) NOT NULL

Customer Number VARCHAR ( 6 ) NOT NULL,

Customer Telephone VARCHAR ( 11 ) ,

Customer Name VARCHAR ( 15 ) NOT NULL,

Returning Customer VARCHAR ( 3 ) NOT NULL,

Address VARCHAR ( 50 ) ,

Notes VARCHAR ( 50 ) ,

PRIMARYKEY ( Complaint Type ) ,

FOREIGNKEY ( Customer Number IDENTIFIES Complaint Type ) )

2.7 Consider the Issue of View Integration

To see the issue of position integrating, add the field “ Complaint Type ” to associations between two entities of “ Purchases ” and “ Customer Details ” for the information demands of Island Dreams.

Customer Detailss

Customer Number

00127

Customer Name

Dave Higgins

Customer Telephone

02098888888

Returning Customer

Yes

Address

27 Acacia Avenue Upper Farringdon Rutlandshire England

Complaint Type

001

Notes

Customer diabetic

Purchases

Merchandise Code

Merchandise Name

Product Type Code

Merchandise Type

Complaint Type

STK01/2004

Holiday 1 on St Kilda 2004 01-06-04 to 17-06-04

HOL

Vacation Box

001

BTH/STK

Boat Hire St Kilda

Bohrium

Boat Hire

002

FE/STK

Fishing Equipment St Kilda

EH

Equipment Hire

003

AC/STK

All conditions vesture St Kilda

EH

Equipment Hire

004

SU01/2004

Holiday 1 on South Uist 18-06-04 to 29-06-04

HOL

Vacation Box

005

BTH/SU

Boat Hire South Uist

Bohrium

Boat Hire

006

Undertaking 3 – Database Management System ( DBMS )

3.1 Using a Database Management System ( DBMS ) set-up all of the above Normalized Tables

Database Management System ( DBMS ) is a set of package plans in computing machine. It ‘s controls the creative activity, care and utilizing of database or an organisation with computing machine as a platform and to the terminal users. Besides, DBMS is to acknowledge the organisations to put control of organisation database development to the database decision makers and other specializers. Besides it is a set of system package to assist to utilize of incorporate aggregation of information records and files known in databases. Furthermore, DBMS besides allows the plans of different user application to entree the same database handily.

Therefore, DBMS can be set of package plans that control the organisation, direction, storage and restore of informations in a database. It ‘s categorization harmonizing to the information constructions or types. The DBMS accepts petitions for informations from an application plan and instructs the operating system to reassign the appropriate informations. The questions and responses must be submitted and received harmonizing to a format that conforms to one or more applicable protocols. So when a DBMS is used, information systems can be changed much more easy as the organisation information ‘s demands change. New classs of informations can be to add in the database have n’t break to the bing system.

3.2 Function of a Database management system:

CRUD Functions – Create, Read, Update and Delete.

Data Dictionary – A DBMS must back up a depository of meta-data – informations about informations. This depository is known as a information lexicon or system catalog.

Transaction direction – Database management system must offer support for the construct of a dealing and must pull off the state of affairs of multiple minutess impacting against a database.

Concurrency control – enable many users to portion informations in a database – to entree informations at the same time.

Recovery – guarantee that the database is able to retrieve from hardware or package failure, which causes that database to be damaged in some manner.

Authorization – provide installations fro the enforcement of security.

Data Communication – must be able to incorporate with communications package running in the context of an IT system.

Data Integrity – ensures that it remains an accurate contemplation of its UoD.

Administration Utilities – ensures that there are sufficient installations available for the disposal of a database:

Facilities for importing informations

Facilities for exporting informations

Facilities for supervising the use and operation of a database

Facilities for supervising the public presentation of a database and for heightening this public presentation

A DBMS contain four chief parts:

Define information construction

Modeling linguistic communication

Database question linguistic communication

Transaction mechanisms:

3.3 Components of DBMS:

Data Definition Subsystem helps us to make, keep and utilize of the informations lexicon and the construction of the files in a database.

DBMS Engine receive logical petition from the assorted sorts DBMS subsystems, converts them in to physical equivalent, and really accesses the informations lexicon and database as they exist on a storage device.

Data Manipulation Subsystem helps us to add, alteration and delete information in a database. Software tools within the informations use subsystem are most often the primary interface between user and the information contained in a database. It allows user to explicate its logical information demands.

Data Administration Subsystem helps us to treat the overall database environment by supplying installations for backup and recovery, security direction, concurrence control and direction changed.

Application Generation Subsystem includes installations to assist us to develop transactions-intensive applications. It usually requires that user execute a elaborate series of undertakings to treat a dealing. It installations to utilize informations entry screens, interface and scheduling linguistic communications easy.

3.4 Using a Microsoft Access Database Management System ( DBMS )

3.4.1 Customer Detailss

3.4.2 Purchases

Undertaking 4 – Structured Query Language ( SQL )

4.1 Set-up and prove the full followers question utilizing Structured Query Language ( SQL ) .

Structured Query Language ( SQL ) is a computing machine linguistic communication ; this is designed for pull offing informations in a relational database direction system. It lets users to entree and pull strings the database. The undermentioned undertaking is to expose how the Structured Query Language helps users to pull offing informations in the Academic Human resources section system. An SQL question manifests itself as a series of bids or statements. Question may incorporate arithmetic computations and can utilize query elements or processs stored in the system. So the SQL databases are designed from utilizing SQL questions.

The SQL linguistic communication is function to inquire database inquiries for the informations stored inside the database: “ what ‘s the value of the component stored in “ Name ” index? ” or “ what the relationship between the information “ name ” is and “ salary ” ? ” . Manipulative bids, like “ insert information ” or “ update record ” are besides issued utilizing SQL questions. A Data Definition Language allows SQL questions to make or pull off the information constructions, the ‘tables ‘ of informations, which constitute the database. The existent sentence structure used is a little more rigorous and regimented than these natural linguistic communication phrases suggest, but in consequence this is the type of communicating that will take topographic point between a relational database and the individual or device questioning it utilizing SQL.

SQL – Data Definition

SQL ISO / ANSI Standards

4.2 Creating Tables

To organize a tabular array utilizing SQL we need four constituents:

The name of the tabular array

The name of each of the columns in the tabular array

The information type of each column

The maximal length of each column

These four points are put together in a CREATE TABLE bid

The most common operation in SQL is the question, which is performed with the declaratory SELECT statement. SELECT retrieves informations from one or more tabular arraies, or looks. Standard SELECT statements have no relentless consequence on the database. Some no-standard implemntation of SELECT can hold relentless effects, such as the SELECT INTO sentence structure that exists in some database.

Provide printouts of SQL codification for each question and the end product produced of the names and references of the clients of Island Dreams and the purchases when run the question in the database you have developed:

4.3 Display the names and reference of the clients

4.4 Display the purchases

Expose a full record of a ailment. This should include the name of the client, the ailment type, and the result of the ailment.

4.5 Display full record of a ailment

Undertaking 5 – Analyzing, Designing & A ; Implementing Database

For the design the Island Dreams database system, we have made some premises before the execution of the database. The entity relationship schematization is created to place each entity, relationship and properties. The premises are the follows:

We must necessitate to delegate vet to any new assignment.

Set all the primary keys and foreign keys in all of the tabular arraies, each tabular array has their alone ID. Each tabular array should hold a primary key, and each tabular array can hold merely on primary key

In the undertaking 1, for see the issue of position integrating ; we need to add the field “ Complaint Type ” to associations between two entities of “ Purchases ” and “ Customer Details ” for the information demands of Island Dreams.

In the undertaking 2, we need to bring forth the resulting tabular arraies for the primary and foreign key

In the undertaking 3, we are utilizing DBMS to setup normalized tabular arraies and designed trial informations.

In the undertaking 4, we are utilizing SQL statements base to question our database to look into the database system can be fulfills the demands or non

Base on utilizing the Entity relationship informations theoretical account that describes the content and construction of the informations aid by the section. At the existent environment, we can see the database on three degrees of abstraction: external, conceptual, and internal.

The external degree besides has the users ‘ positions of the database. This is depending on their demands, different users ‘ entree different parts of the database.

To see the issue of position integrating, no field in associations between two entities of “ Purchases ” and “ Customer Details ” for the information demands of Island Dreams. So the database system of Island Dream is non to be integrated to maintain records on clients ‘ inside informations and their purchases of peculiar merchandises. Its must add the field of “ Complaint Type ” in relation to the engagement of entities in a relationship. As a information theoretical account is an of import constituent portion of any information systems specification, so we needs to supply the systematize database to Island Dreams for utilizing package to hive away many informations of the clients and purchases. In add-on, to implement the above database, Normalization can be used in the procedure of bring forthing a scheme non capable to register care anomalousnesss ; it is usually used as a agency of formalizing and the consequences of critical countries of top-down informations analysis. If the job with a to the full normalized database, it is normally made up of many tabular arraies, Demoralizations can be used to better retrieval or update public presentation is hence to step back from a to the full normalized database and present some of the controlled redundancy.

The database systems have become so of import to organisations that the activity is devoted to be aftering for, monitoring and administrating the systems. We can concentrate on the planning and managerial activities relevant to database. It is defined the construct of informations disposal, the range of the informations disposal map, relate the costs and benefits of holding a information disposal maps. It besides defines the construct of a information lexicon and considers the issue of database security. The informations control is primary map for the database decision maker ( DBA ) . The DBA needs to be able to make three chief things:

Prevent manque users from logging-on to the database

Allocate entree to specific parts of the database to specific users

Allocate entree to specific operations to specific users

To construct a database system, we must utilize the installations of some DBMS to specify suited informations constructions, some package looks like e.g. Microsoft Access or Oracle Database can be used for storage, runing develop and virtualization engineering:

Leave a Reply

Your email address will not be published. Required fields are marked *