Boyce Codd Normal Form 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.

Design the database system for Island Dreams. Island Dreams is a travel company that specialises in organising holidays for people on Britain's offshore islands. They want to develop a system to handle customer complaints. The database system can keep records of customers and their purchases of particular products. It should be able to record complaints and their outcomes.

Entity-relationship diagram

An entity-relationship diagram (ERD) 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

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.

Before analyzing the entities, let have a look the definition of entity.

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.

In this case study, the entities of the Island Dream are identified as followed:

"Customer" is represented to traveler that included name, address, home telephone number and mobile telephone number.

"Product" can be any of the things the company sells such as a holiday package, rental of a vehicle, travel insurance or hire of fishing equipment.

"Purchase" is represented to customer's order included Customer information and Product information

"Complaint" is represented to Customer's complaint included Customer information and Product information and Complaint status or result.

Identifying the relationships for the model:

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.

The Customer (1:M) one-to-many to the Purchase

What is one-to-many and many-to-many Entity Relationship~

One-to-Many Entity Relationship is by far the most common relationship type. It consists of either a one through many or a zero through many notations on one side of a relationship and a one and only one or zero or one notation on the other.

However, we can summarize all the entities to the Entity Relationship Data Model.

Diagram for Entity Relationship Data Model which describes the content and structure of the data held by Island Dreams.

Fig 1 entity relationship diagram for the Island Dreams.

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 to identify the subset of the values in the column, which has been identified as a candidate key.

Table in another term relationship, although there is the difference that a table is usually a multi-set (bag) as a series, and does not allow copies. 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.

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 which says that the primary key field can not be empty and can not 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.

Foreign keys:

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

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.

First normal form to second normal form:

To move form first normal form to second normal form I remove part-key dependencies. "A relation is in second normal form if and only if it is first normal form every non key attributes is fully functionally dependent on the primary key"

Take for instance the table named "Customer" here I have a tow compound key Customer_Number and Product_Code. Customer_Number and Product_Code have influence on the Customer. Hence I brake out the determinant and dependent data items into their own table.

Second normal form to third normal form:

To move form second normal forms to tired normal form I remove inter-data dependences (non-key dependences).

"A relation is in third normal form if and only if it its in second normal form and every non-key attribute is non-transitively dependent on the primary key"

A use full mnemonic for remembering the relation for normalization is the distortion of the legal Oath presented below:

No repeating group

The data items depended upon the key.

The while key And nothing but the key

So help me code.

Boyce-Codd Normal Form (BCNF)

A relation is in Boyce-Codd normal form (BCNF) if and only if every one of its important functional dependencies is a candidate key. The definition of BCNF is handling certain situations which 3NF does not deal with. To distinguish the characteristics of a relation between 3NF and BCNF are given below. Since it is such a relationship is unlikely to have these properties, in the real life design the most case of the relations in 3NF are also in BCNF. Therefore many authors propose a vaguely distinction between 3NF and BCNF when it involves about giving advice to normalize a design for long term. As the relations in 3NF and BCNF are slightly difference, it is a bit difficult to bring up with significantly examples. To be strictly conforming to the definition of 3NF does not handle a relation that:

1. Has multiple candidate keys, which

2. Those candidate keys are composite, and

3. The candidate key overlap. For example, the candidate keys have at least one common attribute.

Produce the resulting tables clearly indicating the primary and foreign keys.

Here are all tables after normalization, highlight in Yellow is Primary Key and Highlight in Blue is Foreign Key:

Customer_Number

Customer_Name

Customer_Address

Customer_Telephone

Customer_Return

Customer_Notes

Table 2.1 Customer

Purchase_Number

Customer_Number

Product_Code

Table 2.2 Purchase

Product_Code

Product_Name

Product_TypeCode

Product_Type

Table 2.3 Product

Complaint_Number

Complaint_Outcome

Purchase_Number

Table 2.4 Complaint

Data Dictionary

A data dictionary is a collection of descriptions of the data objects or items in a data model for the benefit of programmers and others who need to refer to them. A first step in analyzing a system of objects with which users interact is to identify each object and its relationship to other objects. This process is called data modeling and results in a picture of object relationships. After each data object or item is given a descriptive name, its relationship is described (or it becomes part of some structure that implicitly describes relationship), the type of data (such as text or image or binary value) is described, possible predefined values are listed, and a brief textual description is provided. This collection can be organized for reference into a book called a data dictionary.

In this case study, the data dictionary is identified as followed:

Data Element Entry

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

1

Type

Element

Name

Customer_Number

Alias

Customer No

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

2

Type

Element

Name

Customer_Name

Alias

Customer Name

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

3

Type

Element

Name

Customer_Address

Alias

Address

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

4

Type

Element

Name

Customer_Telephone

Alias

Customer Telephone

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

5

Type

Element

Name

Customer_Return

Alias

Returning Customer

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

6

Type

Element

Name

Customer_Notes

Alias

Notes

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

7

Type

Element

Name

Product_Code

Alias

Product Code

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

8

Type

Element

Name

Product_Name

Alias

Product Name

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

9

Type

Element

Name

Product_TypeCode

Alias

Product Type Code

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

10

Type

Element

Name

Product_Type

Alias

Product Type

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

11

Type

Element

Name

Purchase_Number

Alias

Purchase Number

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

12

Type

Element

Name

Complaint_Number

Alias

Complaint Number

Definition

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

13

Type

Element

Name

Complaint_Outcome

Alias

Complaint Outcome

Definition

Occurrence

One value per item

Picture

Notes

Data Structure Entry

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

1

Type

Structure

Name

Customer

Alias

Customer Table

Definition

Customer = Customer_Number + Customer_Name +

Customer_Telkephone + Customer_Address + Customer_Return + Customer_Notes

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

2

Type

Structure

Name

Product

Alias

Product Table

Definition

Product = Product_Code + Product_Name +

Product_TypeCode + Product_Type

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

3

Type

Structure

Name

Purchase

Alias

Purchase Table

Definition

Purchase = Purchase_Number + Customer_Number +

Product_Code

Occurrence

One value per item

Picture

Notes

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

4

Type

Structure

Name

Complaint

Alias

Complaint Table

Definition

Complaint = Complaint_Number + Purchase_Number +

Complaint_Outcome

Occurrence

One value per item

Picture

Notes

Data Store Entry

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

Type

Store

Name

Island Dreams

Alias

Island Dreams system

Definition

Island Dream = Customer + Product +

Purchase + Complaint

Occurrence

Picture

Notes

Data Flow Entry

Data

Dictionary

Title

System

Island Dreams

Document

Name

Sheet

Type

Flow

Name

Purchase Enquiry

Alias

Order Enquiry

Definition

Purchase = Purchase_Number + Product_code + Customer_Number

Occurrence

Picture

Notes

Source : from Customer table and Product Table

Destination: Enquiry from Purchase table

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.

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

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 into 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 users 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 users 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 interfaces.

I am using MySQL 5.0 to setup normalized tables and designed test data.

Table: Customer

Table: Purchase

Table: Product

Table: Complaint

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 "Customer" and "Product"?". 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.

Provide printouts of SQL code for each query and the output produced when you run the query in the database you have developed:

Display the names and addresses of the customers of Island Dreams and the purchases that they have made.

Below is the SQL code for create the table.

SELECT Customer.Customer_Name, Customer.Customer_Address, Product.Product_Name

FROM (Purchase INNER JOIN Customer ON Purchase.Customer_Number = Customer.Customer_Number) INNER JOIN Product ON Purchase.Product_Code = Product.Product_Code

Customer_Name

Customer_Address

Product_Name

Dave Higgins

27 Acacia Avenue Upper Farringdon Rutlandshire England

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

Dave Higgins

27 Acacia Avenue Upper Farringdon Rutlandshire England

Boat Hire St Kilda

Dave Higgins

27 Acacia Avenue Upper Farringdon Rutlandshire England

Fishing Equipment St Kilda

Dave Higgins

27 Acacia Avenue Upper Farringdon Rutlandshire England

All weather clothing St Kilda

Dave Higgins

27 Acacia Avenue Upper Farringdon Rutlandshire England

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

Dave Higgins

27 Acacia Avenue Upper Farringdon Rutlandshire England

Boat Hire South Uist

Alan Vettel

2B Sun Yee Mansion, 111 Fuk Wing Street Hong

Fishing Equipment St Kilda

David Webber

128 Golden Avenue

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

Ryan Smith

1 Radio Tower, China

Boat Hire South Uist

Stephen Chow

133 King's Road , Quarry bay

Boat Hire South Uist

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

Below is the SQL code for create the table.

SELECT Complaint.* , Customer.Customer_Name , Product.Product_Type

FROM Complaint INNER JOIN (Purchase INNER JOIN Customer ON Purchase.Customer_Number = Customer.Customer_Number INNER JOIN Product ON Purchase.Product_Code = Product.Product_Code) ON Complaint.Purchase_Number = Purchase.Purchase_Number

;

Complaint_Number

Complaint_Outcome

Purchase_Number

Customer_Name

Product_Type

1

Refund ABTA

1

Dave Higgins

Holiday Package

2

Refund ABTA

3

Dave Higgins

Equipment Hire

3

Refund

5

Dave Higgins

Holiday Package

4

Refund ABTA

7

Alan Vettel

Equipment Hire

5

Refund

9

Ryan Smith

Boat Hire

Assumptions and Improvements

Assumption

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 Product to any new Purchase.

Since each Product only can work at one Order (Purchase) at a time and they can specialize more than one type of Product. This is means all products that can for any type of Product to make Purchase.

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

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

In the task 4, we are using SQL statements base on MySQL 5.0 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.

Improvements

Object-orientation and databases

Both object-oriented programming and relational database management systems (RDBMSs) are extremely common in software today. Since relational databases do not store objects directly (though some RDBMSs have object-oriented features to approximate this), there is a general need to bridge the two worlds.

The core of object-relational thinking is the ability to incorporate greater levels of abstraction into data models. This idea represents a major shift in the way that data modeling is done. Current relational databases are usually highly normalized but with little abstraction. Each "thing of interest" is instantiated as a relational table. As a result, systems frequently require numerous database tables and an equal number of screen modules and reports. The program modules are usually based directly on these tables with user workflow only instantiated through the way that the hundreds of screen modules interact. The object-oriented (OO) approach to data modeling will be something of a change for people familiar with entity relationship modeling. Even though we still end up with tables and relationships at the end of the process, the way to think about the modeling process has to change. Object-relational data models have several advantages over traditional data models: They require fewer entities (or "classes" in object-oriented terminology); They are more robust, in that they will support not only the specific user requirements gathered during the analysis phase, but will also usually support a broader class of requirements; They are more stable in that, as new requirements arise, the models will require fewer changes than traditional models

Data is not "information" unless it is valued. Information value provides "profit or gain" only when accessible or used. Accessibility and use, through organized systems, provides "competitive advantage". Speed determines the degree of competitive advantage. Computerized database systems are thus, the ultimate method of high-speed information retrieval. It is not difficult to build an organized database system. The "difficulty" lies in the laborious, mundane task of collecting, categorizing and maintaining the massive amounts of data.

Information is not valued unless it is legitimate. It must be valid and true to be worthy of use in decision-making. So, it is critical that all aspects of our system provide quality. To quote statistics based on erroneous data is considered foolish or criminal. The core of object-relational thinking is the ability to incorporate greater levels of abstraction into data models. This idea represents a major shift in the way that data modeling is done. Current relational databases are usually highly normalized but with little abstraction.

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

For the DBMS toolkit to review the database, the interface is essential function of most ICT systems it to interact with users. The three aspects are the content, control and format. The interface can be seen as collection of dialogues between the user and the ICT systems.

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.