DBMSAS2

Details
 Student name : - J.A. Inuka Umayantha Uddeeptha

 SQA ID number : -
 NIC No : - 200702102272
 Subject Name : - DATABASE MANAGEMENT 1
 Subject Code : - H7DX 04/AS/02
 Branch : - Colombo
Question :
EXON (PVT) Ltd. Sri Lanka provides Specialized Software Engineers for other Companies on temporary
basis to work for their projects. Worked time of EXON Software Engineers at other companies are
recorded in the following table NICN (National Identity Card Number) is used in EXON to identify their
Software Engineers Uniquely.
1. What is Normalization?
 Process of Organizing Data was called Database Normalization
 Normalization is used to eliminate undesireble characteristics like Insertion ,
Update and Deletion Anomalies and Minimize the redundancy.
 Simply it divides larger tables into smaller and make relationships with other
tables
2. Define 1st Normal Form, 2nd Normal Form and 3rd Normal Form?
 1st Normal Form
In First Normal Form We Bring Multi Valued Attributes to Atomic

Level , as a example we can put full name into first_name and last_name columns.
 2nd Normal Form
If we need to turn database into 2nd Normal Form first we must turn database into
1st Normal Form , In this level remove unwanted data that duplicated in table and
create new table and add them into that table.
 3rd Normal Form
As a 2nd Normal Form Also first we need to turn database into 2nd Normal Form to
turn database into 3rd Normal Form , In this level we make relationships with
tables that we created in 2nd Normal Form.
3. To which normal form this table belongs to?

 1st Normal Form
4. Identify the Primary key of this relation and justify your selection?
 We can create eid column and we can use that column as a Primary Key
In this case all the column data were duplicated so we have to make specific id for
each row we can make column that named eid and set it as primary key and auto
increment because primary key cannot be duplicated
5. Identify Fully Functional Dependencies and Partial Dependencies on the Primary Key?
 Fully Functional dependencies :- Employee Name
 Partial Dependencies :- Contract No , Employee Name
Company ID, Employee Name
6. Normalise the above mentioned table to 2nd Normal Form
eid nicn contract_no Hours employee_name

cid company_id company_location
1 892660522V IT 2014 27 A.Janith
2 880944993V IT 2014 84 T.Tharaka 1 IT 001 Kandy
3 842564347V IT 1995 42 C.Asela 2 IT 120 Colombo

4 892660522V IT 1995 42 A.Janith
CREATE TABLE èmployee` (èid`
INT NOT NULL ,`nicn` VARCHAR(10) NOT NULL ,`contract_no` VARCHAR(10) NOT
NULL ,`hours` INT ,èmployee_name `VARCHAR(45) NOT NULL , );
CREATE TABLE `company` (`cid` INT NOT NULL , `company_id` VARCHAR(10)
NOT NULL , `company_location` VARCHAR(45) NOT NULL,);
INSERT INTO èmployee` (èid` ,`nicn`, `contract_no`, `hours`,` employee_name`) VALUES
(‘1’,’ 892660522V’,’ IT 2014’,’ 27’,’ A.Janith’);
INSERT INTO èmployee` (èid`, `nicn`, `contract_no`, `hours`, èmployee_name`) VALUES
(‘2’,’ 880944993V’,’ IT 2014’,’ 84’,’ T.Tharaka’);
INSERT INTO èmployee` (èid` ,`nicn`, `contract_no` ,`hours` ,èmployee_name`) VALUES
(‘3’,’ 842564347V’,’ IT 1995,’ 42’,’ C.Asela’);
INSERT INTO èmployee` (èid`,` nicn`, `contract_no`, `hours`, èmployee_name`) VALUES
(‘4’,’ 892660522V’,’ IT 1995’,’ 42’,’ A.Janith’);
INSERT INTO `company` (`cid` ,`company_id` ,` company_location `) VALUES (‘1’,’ IT 001’,’
Kandy’);

Colombo’);
PK(èmployee`)->eid PK(`company`)->cid
7. Identify the Transitive Dependencies on above 2nd Normal Form tables
 NICN -> Hours
8. Normalise above mentioned tables to 3rd Normal Form

Employee Details
eid
2
3
4 nicn contract_no hours employee_name
892660522V IT 2014 27 A.Janith
880944993V IT 2014 84 T.Tharaka employee_eid company_cid

1 1
842564347V IT 1995 42 C.Asela
2 1
892660522V IT 1995 42 A.Janith
3 2
4 2
Employee has company
Many to Many relationship
Company Details
cid company_id company_location
1 IT 001 Kandy
2 IT 120 Colombo
CREATE TABLE èmployee` (èid` INT NOT NULL ,`nicn` VARCHAR(10) NOT
NULL ,`contract_no` VARCHAR(10) NOT NULL ,`hours` INT ,èmployee_name
`VARCHAR(45) NOT NULL , );
CREATE TABLE `company` (`cid` INT NOT NULL , `company_id` VARCHAR(10)
NOT NULL , `company_location` VARCHAR(45) NOT NULL,);
INSERT INTO èmployee` (èid` ,`nicn`, `contract_no`, `hours`,` employee_name`) VALUES
(‘1’,’ 892660522V’,’ IT 2014’,’ 27’,’ A.Janith’);
INSERT INTO èmployee` (èid`, `nicn`, `contract_no`, `hours`, èmployee_name`) VALUES
(‘2’,’ 880944993V’,’ IT 2014’,’ 84’,’ T.Tharaka’);
INSERT INTO èmployee` (èid` ,`nicn`, `contract_no` ,`hours` ,èmployee_name`) VALUES
(‘3’,’ 842564347V’,’ IT 1995,’ 42’,’ C.Asela’);
INSERT INTO èmployee` (èid`,` nicn`, `contract_no`, `hours`, èmployee_name`) VALUES
(‘4’,’ 892660522V’,’ IT 1995’,’ 42’,’ A.Janith’);
Kandy’);
PK(èmployee`)->eid PK(`company`)->cid
There isn’t any query to make relationships in tables after creating many to many relationship
between employee and company table (by graphical method) automatically made table called
employee has company and the foreign keys of that table are employee_eid and company_cid .
9. Design an ER diagram for 3rd Normal Form

End !

DBMSAS2

Uploaded by

Copyright:

Available Formats

DBMSAS2

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMSAS2

Uploaded by

Copyright:

Available Formats

Details

 Student name : - J.A. Inuka Umayantha Uddeeptha

In First Normal Form We Bring Multi Valued Attributes to Atomic

 2nd Normal Form

 3rd Normal Form

3. To which normal form this table belongs to?

6. Normalise the above mentioned table to 2nd Normal Form

eid nicn contract_no Hours employee_name

2 880944993V IT 2014 84 T.Tharaka 1 IT 001 Kandy

3 842564347V IT 1995 42 C.Asela 2 IT 120 Colombo

INSERT INTO `company` (`cid` ,`company_id` ,` company_location `) VALUES (‘2’,’ IT 120’,’

8. Normalise above mentioned tables to 3rd Normal Form

4 nicn contract_no hours employee_name

892660522V IT 2014 27 A.Janith

880944993V IT 2014 84 T.Tharaka employee_eid company_cid

cid company_id company_location

9. Design an ER diagram for 3rd Normal Form

You might also like