HND Database Design Concepts Assignment

DATABASE DESIGN CONCEPTS ASSIGNMENT

Part 1

Question 1

The internal schema describes how the data will be physically stored and accessed, using the facilities provided by a particular DBMS. For example, the data might be organized so that all the insurance policies belonging to a given customer were stored close together, allowing them all to be retrieved into the computer memory in a single operation. The internal level is like a space where data are floating in the space e.g pool of data. The internal level is still the logical view.

The conceptual schema (sometimes called the logical schema) describes the stored data in terms of the data model of the DBMS. In a relational DBMS, the conceptual schema describes all relations that are stored in the database. Table definition or data definition are been used here e.g the create table and drop table.

The external schemas specify views that enable different users of the data to see it in different ways. As a simple example, some users of policy data might not require details of the commission paid. By providing them with a view that excludes the Commission Rate column, we would not only shield them from unwanted (and perhaps unauthorized) information, but also insulate them from changes that might be made to the format of that data. . Data manipulation is use to create SQL in a programming language e.g. SELECT. SQL have three languages, which are data manipulation, data definition and data management like granting, revoking, creating index.

The advantages of the three-tiered architecture are division into levels allows both developers and users to work on their own levels. They do not need to know the details and changes in the other levels. Meaning each level has its own independence. This three-schema helps us to provide data security of data’s among different users accessing the database, ensures data integrity and avoid duplication of data’s in the database. It helps us to establish and maintain relationship among the data’s in the database.

Part 1

Question 2

Logical independence arises in a database when application programs are written on external levels. We can change the conceptual level by adding columns to tables or by adding tables to the database and no application code needs to be re-written.

Data independence is the capacity to change the schema at one level of a database system without having to change the schema at the next higher level. If the schema at one level is changed, and the mappings to the next higher level are changed, the schema at the next level can remain unchanged. And there are two types of data independence namely logical data independence and physical data independence.

Logical data independence is the capacity to change the conceptual schema without having to change the external schemas or application programs. Only the view definition and the mappings need to be changed in the DBMS that supports logical data independence.

Physical data independence is the capacity to change the internal schema without having to change the external schemas.

Data independence is accomplished because, when the schema is changed at one level the schema at the next higher-level remains unchanged only the mapping between the two levels is changed. 

Part 1

Question 3

Database is an integrated collection of data organized to meet the needs of one or more users. Databases are used today in commercial and non- commercial environment.

Commercial Environment	Non- commercial Environment
Banks	Government Bodies
NHS-Hospitals	Libraries
Educational Sectors	Churches and Religious Organizations
Telecom Sector	Home Office
Web Industries	Security Industry
DVLA/TV Licenses	Charity Organizations
Aviation Industry

Banks- use database to keep information about customers and their accounts. All customer details like name, address, phone numbers, date of birth etc are recorded in the bank database when customers open an account in the bank. This record will be kept in the database and can be updated anytime there is a change in details.

NHS- holds a very huge database of all patients. Each time you visit your GP, he/she call up your details on the database. All NHS work are really done on computer using database. E.g. transplanting, radiology etc.

Educational sector- University, colleges and other learning institutions store student and staff information on their database and can be updated if their details changes.

Airline- The aviation industry uses databases to also store details of customers, reservation systems for booking airline flights and other travel arrangement. It is also a huge database that can be seen and use all over the world. You can access the database anywhere to book or cancel your reservations.

DVLA/TV licenses- DVLA and TV Licences store customer information on their database to use whenever they need for licensing systems such as drivers and vehicles, and to keep track of all people been given licences. TV licences on the other hand uses database to also store information about everyone that purchases TV in this country and keep a record of update.

Home Office- In non-commercial environment like home office, people’s details are kept in a huge database. These details are used when processing applications and other issues. Although home office database are very huge because they handle almost everybody details in this country plus the EU database. These entire databases are link to many other organizations especially if the governments want to track down someone records. 

SIA (security industry authority) also store customers and staff information in their database and retrieve to use at anytime. They uses it for application processing and granting of licence etc.

Library- libraries hold information of everyone that uses their library in their database. E.g inventory systems raging from the books held in a library

Other Government offices- like Inland Revenue and NIC also hold large amount of details of information of people on their database. For example, before you work in this country, you must apply for a national insurance number and your details will be saved on their database so also Inland Revenue that look into taxes of everyone.

Churches keep their members information in their databases. That information could be used to send personal emails or general updating purposes through emailing.

Part 2

Question 1

1St Normal Form

Customer Table Customer Number Customer Name Customer Address	Order Table Order Number Order Date Required Date
Department Table Department Code Department Name Department Rate Job Number Job Duration

Above is a 1st Normal Form: Primary and Foreign Keys appear

2nd Normal Form

Customer Table Customer Number Customer Name Customer Address	Order Table Order Number Order Date Required Date
Department Table Department Code Department Name Department Rate	Job Table Job Number Job Duration

Above is a 1st Normal Form: Some parts of tables repeat themselves

3rd Normal Form

Customer Table Customer Number Customer Name Customer Address	Order Table Order Number Order Date Required Date Customer Number FK
Department Table Department Code Department Name Department Rate	Job Table Job Number Order Number FK Department Code FK Job Duration
Job Cost Table Job Cost Number PK Cost Number FK Order Number FK Job Number FK Department Code FK Invoice Number FK	Invoice Table Invoice Number Order Number Invoice Date

ERD