PLEASE PROVIDE SCREENSHOTS

Instructions

For this lab we will be taking our various tables created from our normalization process and entering them into Oracle. For each of your tables you will use SQL and the CREATE TABLE command to create each table with it's various attributes (columns). You will then use the INSERT INTO command to add 5 rows of data (you make up your own data) to populate each table. After creating the tables and entering the data - use the SELECT * FROM tablename to list all of the data in each table. You can do a screen capture to save the table data. Turn in each "screen shot" of each of your tables with all of the data. Attached files are the instructions of Virtual Machine and Oracle Databases. Please read the Word document first and then watch the instructional video to learn how to create tables in Oracle database. You are also welcomed to use Microsoft SQL Server to complete this lab. It is free to download and use for practical purpose. https://www.microsoft.com/en-us/sql-server/sql-server-downloads The latest version is SQL Server 2017. You can choose either "developer" or "express" version. Oracle and MS SQL Server are the two major DBMS on the market.

Employee(Eid,Ename,Address,Phone,Type,Salary,Dname,Daddress)

Functional Dependency

1.Eid->{Ename,Address,Phone, Type,Renumeration,Dname,Daddress }

2.Eid->{Type}

3.Dname->Daddress

4.Type->Renumeration

As Phone is a multivalued attribute so Employee table is not in 1NF.

1NF Normalization

1.EmployeePhone(Eid,Phone)

2. Employee(Eid,Ename,Address,Type,Renumeration,Dname,Daddress)

Table 2 is 2NF but not in 3 NF as it violates transitive dependency

As from functional dependency1 and 3

Eid->Dname

Dname->Daddress

From functional dependency1 and 4

Eid->Type

Type->Renumeration

3NF Normalization

Employee(Eid,Ename,Address, Type,Dname)

EmployeeType(Type,Renumeration)

Department(Dname,Dlocation)

The resulting tables in 3NF are

EmployeePhone(Eid,Phone)

Employee(Eid,Ename,Address, Type,Dname)

EmployeeType(Type,Renumeration)

Department(Dname,Dlocation)

Lab(LabEmployeeId,TechnicianID,EngineerID,TypeofEngineer,AdminID,TypeofAdmin,SkillPRogrammerID,TypeofSkillProgrammer)

FunctionalDependency

1. LabEmployeeId->{TechnicianID,EngineerID,AdminID,SkillProgrammerID}

2.EngineerID->Typeofengineer

3. AdminID->TypeofAdmin

4.SkillProgrammerID->TypeofSkillProgrammer

The table is in 1NF and 2NF but not in 3NF as transitive dependency is not satisfied

3NF normalization

LabEngineer(EngineerID,Typeofengineer)

LabAdmin(AdminID,TypeofAdmin)

LabSkillProgrammer(SkillProgrammerID,TypeofSkillProgrammer)

Lab(LabEmployeeId, TechnicianID, EngineerID, AdminID, SkillProgrammerID)

Foreign keys EngineerID,AdminID and SkillPRogrammerID references LabEngineer(EngineerID),LabAdmin(AdminID), LabSkillProgrammer(SkillProgrammerID) respectively.

So the tables are:

LabEngineer(EngineerID,Typeofengineer)

LabAdmin(AdminID,TypeofAdmin)

LabSkillProgrammer(SkillProgrammerID,TypeofSkillProgrammer)

Lab(LabEmployeeId, TechnicianID, EngineerID, AdminID, SkillProgrammerID)

Patient(SSn,Pname,City,InsuranceNumber,Did,Dname,illness,Dateand Time,Bednumber,Medicine)

The functional dependencies are:

1.SSn->{Pname,City,Insurancenumber}

2.Did->Dname

3.SSn,Illness->Did,DateandTime,Bedno

4.Did,Illness->Medicine

2NF

1.Patient(SSn,Pname,City,InsuranceNumber,Did,Dname)

2Bed(SSn,Illness,Did,DateandTime)

3.Treatment(SSn,Illness,Medicine,Bedno)

As Functional Dependency 2 violates transitive dependency it in not in 3NF

3NF

Doctor(Did,Dname)

Patient(SSn,Pname,City,InsuranceNumber,Did) Foreign keyDid references Doctor(Did)

Bed(SSn,Illness,Did,DateandTime,Bedno) Foreign key SSn references Patient(SSn)

Treatment(Did,Illness,Medicine) Foreign key Did references Doctor(Did)

So the tables are

Doctor(Did,Dname)

Patient(SSn,Pname,City,InsuranceNumber,Did)

Bed(SSn,Illness,Did,DateandTime,Bedno)

Treatment(Did,Illness,Medicine)

LabEngineer(EngineerID,Typeofengineer)

LabAdmin(AdminID,TypeofAdmin)

LabSkillProgrammer(SkillProgrammerID,TypeofSkillProgrammer)

Lab(LabEmployeeId, TechnicianID, EngineerID, AdminID, SkillProgrammerID)

EmployeePhone(Eid,Phone)

Employee(Eid,Ename,Address, Type,Dname)

EmployeeType(Type,Renumeration)

Department(Dname,Dlocation)