Thursday 14 April 2011

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

No comments:

Post a Comment