Er Diagram And Data Dictionary Computer Science Essay

Published:

An entity-relationship diagram is a data modeling technique that creates a graphical representation of the entities, and the relationships between entities, within an information system. This diagram is often used as a way to visualize a relational database: each entity represents a database table, and the relationship lines represent the keys in one table that point to specific records in related tables. ERDs may also be more abstract, not necessarily capturing every table needed within a database, but serving to diagram the major concepts and relationships.

Definition of relationship

A relationship is some association between entities. Relationship is shown by line between entities. Relationship lines indicate that each instance of an entity may have a relationship with instances of the connected entity, or vice versa.

Definition of entity attribute

An entity is characterized by a number of properties or attributes. Values assigned to attributes are used to distinguish one entity from another.

Definition of Optionality and Cardinality

Lady using a tablet
Lady using a tablet

Professional

Essay Writers

Lady Using Tablet

Get your grade
or your money back

using our Essay Writing Service!

Essay Writing Service

Symbols at the ends of the relationship lines indicate the "Optionality" and the "Cardinality" of each relationship. "Optionality" expresses whether the relationship is optional or mandatory. "Cardinality" expresses the maximum number of relationships.

An entity can define as a thing which an organization recognizes as being capable of an independent existence and which can be uniquely identified. In addition, an entity is an abstraction from the complexities of some domain. 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 link between to entities, and it tells us something about which relationships exists between our entities. In the ER approach, more than one relationship can exist between any two entities.

To consider the issue of view integration, add the field "Complaint Type" to associations between two entities of "Purchases" and "Customer Details" for the data requirements of Island Dreams.

Product

Complaint Type

Purchases

Customer Details

1.2 Data Dictionary

Data dictionary is the main reason to store dictionary information in the database diagram itself is ease of data entry in database management systems, a file that defines the basic organization of a database. A data dictionary include a list of all files in the database, the number of records in each file, and the types and name of each field. Most database management systems keep the data dictionary hidden from users to prevent them from accidentally destroying its contents.

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

A description of base relations, including relation names, column names, column data types and null characteristicsof columns.

Primary-key and foreign key declarations, including propagation constraints.

A description of views.

Declarations of user groups and authoriztions.

Furthemore, data dictionary is a means for recording the meta-data of some organisatioin and its do not contain any actual data from the database, only bookkeeping information for a managing. If without a data dictionary, a database management system cannot access data from the database.

According to model the data requirements of Island Dreams, used the Data Dictionary to store and mange definitions of processes, data flows and data store, to group of related data elements and other data structures.

1.2.1 Customer Details

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

Product Code

STK01/2004

Product Name

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

Product Type Code

HOL

Product Type

Holiday Package

Data Dictionary

Product Code

BTH/STK

Product Name

Boat Hire St Kilda

Product Type Code

BH

Product Type

Boat Hire

Data Dictionary

Product Code

Lady using a tablet
Lady using a tablet

Comprehensive

Writing Services

Lady Using Tablet

Plagiarism-free
Always on Time

Marked to Standard

Order Now

FE/STK

Product Name

Fishing Equipment St Kilda

Product Type Code

EH

Product Type

Equipment Hire

Data Dictionary

Product Code

AC/STK

Product Name

All weather clothing St Kilda

Product Type Code

EH

Product Type

Equipment Hire

Data Dictionary

Product Code

SU01/2004

Product Name

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

Product Type Code

HOL

Product Type

Holiday Package

Data Dictionary

Product Code

BTH/SU

Product Name

Boat Hire South Uist

Product Type Code

BH

Product Type

Boat Hire

Task 2 - Resulting Tables of the Primary and Foreign Keys

2.1 Normalized Tables

Table is a data (value), which is the model of the vertical columns (which identifies the name) and the horizontal lines. A specified number of columns in the table, but may be any number of rows. Each row is to identify the subset of the values in the column, which has been identified as a candidate key.

In addition, the actual data rows, the panels are generally associated with some other meta-data, such as restrictions on the table or the values in columns.

2.2 Definition of Normalization

Database normalization is a technique to reduce to the minimum duplication of information for designing relational database tables and in doing so to maintain the database for certain types of structural and logical problems, that is to say data anomalies. For example, when a given piece of information has multiple instances to be found in a table, this possible happen that these instances will not be kept consistent during the data in the table is updated, leading to a loss of data integrity. The such kind of problems are less vulnerable to a sufficiently normalized table, because its structure reflects the basic assumptions of the multiple instances of the same information should be used when a single instance of only.

There are rules for UNF, 1NF, 2NF, 3NF, BCNF, 4NF, 5NF and domain-Key NF. Most textbooks mention 5NF and DKNF only in passing and note that they are not particularly applicable to be design process. Normalization is really about the "formalism of simple ideas". All too often, the simplicity is lost in esoteric terminology and papers are "often excessively concerned with the formalism and provide very practical insight".

In this project, why need a normalization the database, it is because normalization is about designing a "good" database i.e. a set of related tales with a minimum of redundant data and no update, delete or insert anomalies.

Normalization is a "bottom up" approach to database design, the designer interviews users and collects documents - reports etc. The data on a report can be listed and then normalized to produce the required tables and attributes.

2.3 Stage of Normalization

Collect the data-set - the set of data-items

Transform the normalized data-set into tables in first normal form

Transform first normal form tables to second normal form

Transform second normal form tables to third normal form

Transform third normal form to Boyce-Codd normal form

Transform third normal form to fourth normal form

Transform fourth normal form to fifth normal form

2.4 Primary key:

Primary key is a field or combination of fields that uniquely identify a record in the table, so each tag can be placed without confusion.

Primary key is the field (s) (primary key can be made up of more than one field) that uniquely identifies each record, ie the primary key is unique to each record and the value will never be duplicated in the same table. A constraint is a rule that defines what data are valid for the area. So the primary key constraint is the rule that says that the primary key field cannot be empty and cannot contain duplicate data.

Database systems usually have more than one table, and these are usually related in any way. For example, a customer table and an Order table relate to each other on a unique customer number. The customer table will always be a record for each customer, and the Order table has a record for each order that the customer has.

2.5 Foreign keys:

Lady using a tablet
Lady using a tablet

This Essay is

a Student's Work

Lady Using Tablet

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

Examples of our work

A foreign key (sometimes referred to as the reference key) is a key used to link two tables together. Typically, you will have a primary key field from one table and paste it into another table, which becomes the foreign key (the primary key in the original table).

A foreign key constraint that the data in the foreign keys must be consistent with the primary key of the table is linked. This is called reference integrity is to ensure that data entered is correct and is not unique

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

2.6.1 Customer Details

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

Product Name VARCHAR (50) NOT NULL,

Product Type Code (5) NOT NULL,

Product 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 consider the issue of view integration, add the field "Complaint Type" to associations between two entities of "Purchases" and "Customer Details" for the data requirements of Island Dreams.

Customer Details

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

Product Code

Product Name

Product Type Code

Product Type

Complaint Type

STK01/2004

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

HOL

Holiday Package

001

BTH/STK

Boat Hire St Kilda

BH

Boat Hire

002

FE/STK

Fishing Equipment St Kilda

EH

Equipment Hire

003

AC/STK

All weather clothing St Kilda

EH

Equipment Hire

004

SU01/2004

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

HOL

Holiday Package

005

BTH/SU

Boat Hire South Uist

BH

Boat Hire

006

Task 3 - Database Management System (DBMS)

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

A Database Management System (DBMS) is a set of computer software programs. It controls the creation, maintenance, and the use of the database with computer as a platform or of an organization and its end users. It allows the organizations to place control of organization-wide database development in the hands of database administrators and other specialists. A DBMS is a set of system software. It helps the use of integrated collection of data records and files known as databases. It allows the programs of different user application to access the same database easily.

A DBMS is a set of software programs that controls the organization, storage, management, and retrieval of data in a database. DBMSs are categorized according to their data structures or types. The DBMS accepts requests for data from an application program and instructs the operating system to transfer the appropriate data. The queries and responses must be submitted and received according to a format that conforms to one or more applicable protocols. 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.

3.2 Function of a DBMS:

CRUD Functions - Create, Read, Update and Delete.

Data Dictionary - A DBMS must support a repository of meta-data - data about data. This repository is known as a data dictionary or system catalog.

Transaction management - DBMS must offer support for the concept of a transaction and must manage the situation of multiple transactions impacting against a database.

Concurrency control - enable many users to share data in a database - to access data concurrently.

Recovery - ensure that the database is able to recover from hardware or software failure, which causes that database to be damaged in some way.

Authorization - provide facilities fro the enforcement of security.

Data Communication - must be able to integrate with communications software running in the context of an IT system.

Data Integrity - ensures that it remains an accurate reflection of its UoD.

Administration Utilities - ensures that there are sufficient facilities available for the administration of a database:

Facilities for importing data

Facilities for exporting data

Facilities for monitoring the usage and operation of a database

Facilities for monitoring the performance of a database and for enhancing this performance

A DBMS includes four main parts: data structure, modeling language, database query language, and transaction mechanisms:

3.3 Components of DBMS:

Data Definition Subsystem helps user to create and maintain the data dictionary and define the structure of the files in a database.

DBMS Engine accepts logical request from the various other DBMS subsystems, converts them in to physical equivalent, and actually accesses the database and data dictionary, as they exist on a storage device.

Data Manipulation Subsystem helps user to add, change, and delete information in a database and query it for valuable information. Software tools within the data manipulation subsystem are most often the primary interface between user and the information contained in a database. It allows user to specify its logical information requirements.

Data Administration Subsystem helps user to manage the overall database environment by providing facilities for backup and recovery, security management, query optimization, concurrency control, and change management.

Application Generation Subsystem contains facilities to help user to develop transactions-intensive applications. It usually requires that user perform a detailed series of tasks to process a transaction. It facilities easy-to-use data entry screens, programming languages, and interface.

3.4 Using a Microsoft Access Database Management System (DBMS)

3.4.1 Customer Details

3.4.2 Purchases

Task 4 - Structured Query Language (SQL)

4.1 Set-up and test all of the following queries using Structured Query Language (SQL).

Structured Query Language (SQL) is a computer language, which is designed for managing data in a relational database management system. It lets users to access and manipulate the database. The following task is to display how the Structured Query Language helps users to managing data in the Academic Human resources department system. An SQL 'query' manifests itself as a series of commands or statements. Queries may include arithmetic calculations and can use query elements or procedures stored in the system. SQL databases are designed from using SQL queries.

The SQL language is used to ask database questions for the data stored inside the database: "what is the value of the element stored in "Name" index?" or "what is the relationship between the data "name" and "salary"?". Manipulative commands, like "insert data" or "update record" are also issued using SQL queries. A Data Definition Language allows SQL queries to create or administer the data structures, the 'tables' of data, which constitute the database. The actual syntax used is a little more strict and regimented than these natural language phrases suggest, but in effect this is the type of communication that will take place between a relational database and the person or device querying it using SQL.

SQL - Data Definition

SQL ISO / ANSI Standards

4.2 Creating Tables

To form a table using SQL we need four components:

The name of the table

The name of each of the columns in the table

The data type of each column

The maximum length of each column

These four items are put together in a CREATE TABLE command

The most common operation in SQL is the query, which is performed with the declarative SELECT statement. SELECT retrieves data from one or more tables, or expressions. Standard SELECT statements have no persistent effect on the database. Some no-standard implemntation of SELECT can have persistent effects, such as the SELECT INTO syntax that exists in some database.

Provide printouts of SQL code for each query and the output produced of the names and addresses of the customers of Island Dreams and the purchases when run the query in the database you have developed:

4.3 Display the names and address of the customers

4.4 Display the purchases

Display a full record of a complaint. This should include the name of the customer, the complaint type, and the outcome of the complaint.

4.5 Display full record of a complaint

Task 5 - Analyzing, Designing & Implementing Database

For the design the Island Dreams database system, we have made some assumptions before the implementation of the database. The entity relationship diagramming is created to identify each entity, relationship and attributes. The assumptions are the follows:

We must need to assign vet to any new appointment.

Set all the primary keys and foreign keys in all of the tables, each table has their unique ID. Each table should have a primary key, and each table can have only on primary key

In the task 1, for consider the issue of view integration; we need to add the field "Complaint Type" to associations between two entities of "Purchases" and "Customer Details" for the data requirements of Island Dreams.

In the task 2, we need to produce the resulting tables for the primary and foreign key

In the task 3, we are using DBMS to setup normalized tables and designed test data.

In the task 4, we are using SQL statements base to query our database to investigate the database system can be fulfills the requirements or not

Base on using the Entity relationship data model that describes the content and structure of the data help by the department. At the real environment, we can consider the database on three levels of abstraction: external, conceptual, and internal.

The external level also has the users' views of the database. This is depending on their needs, different users' access different parts of the database.

To consider the issue of view integration, no field in associations between two entities of "Purchases" and "Customer Details" for the data requirements of Island Dreams. So the database system of Island Dream is not to be integrated to keep records on customers' details and their purchases of particular products. Its must add the field of "Complaint Type" in relation to the involvement of entities in a relationship. As a data model is an important component part of any information systems specification, so we needs to provide the systematize database to Island Dreams for using software to store many data of the customers and purchases. In addition, to implement the above database, Normalization can be used in the process of producing a schema not subject to file maintenance anomalies; it is normally used as a means of validating and the results of critical areas of top-down data analysis. If the problem with a fully normalized database, it is usually made up of many tables, Demoralizations can be used to improve retrieval or update performance is therefore to step back from a fully normalized database and introduce some of the controlled redundancy.

The database systems have become so important to organizations that the activity is devoted to planning for, monitoring and administering the systems. We can focus on the planning and managerial activities relevant to database. It is defined the concept of data administration, the scope of the data administration function, relate the costs and benefits of having a data administration functions. It also defines the concept of a data dictionary and considers the issue of database security. The data control is primary function for the database administrator (DBA). The DBA needs to be able to do three main things:

Prevent would-be users from logging-on to the database

Allocate access to specific parts of the database to specific users

Allocate access to specific operations to specific users

To build a database system, we must use the facilities of some DBMS to define suitable data structures, some software looks like e.g. Microsoft Access or Oracle Database can be used for storage, operating develop and virtualization technology: