Limited-time discount for students! | Solutions starting at $6 each

SIT103- Database and Information Retrieval.

Added on - 15 Jun 2021

  • 22

    Pages

  • 2447

    Words

  • 97

    Views

  • 0

    Downloads

Trusted by +2 million users,
1000+ happy students everyday
Showing pages 1 to 6 of 22 pages
SIT103- Database andInformation Retrieval2018 – Assignment 2Student ID:Tutor:
ReportRelational SchemaTable NamePrimary KeyForeign KeyNOT NULLColumnsDepartmentFldDNoDept_LocationsFldDNo, FldLocationFOREIGN KEY (FldDNo)REFERENCESDepartment(FldDNo)EmployeeFldSsnFOREIGN KEY (FldDNo)REFERENCESDepartment(FldDNo)FldSuper_ssnProjectFldPnumberFOREIGN KEY (DNum,Plocation)REFERENCESFldDept_Locations(FldDNo,FldLocation)DependentFldEssn,FldDependent_NameFOREIGN KEY (FldEssn)REFERENCESEmployee(FldSsn)Works_OnFldEssn, FldPnoFOREIGN KEY (FldPno)REFERENCESProject(FldPnumber),FOREIGN KEY (FldEssn)REFERENCESEmployee(FldSsn)Hours(Guru99 2016)(Brombarg P. n.d.)
Create TablesDepartment TableCREATE TABLE Department(FldDNo NUMBER NOT NULL,FldDName VARCHAR2(20) NOT NULL,FldMgr_ssn Number NOT NULL,FldMgr_start_date date NOT NULL,CONSTRAINT "DEPT_PK" PRIMARY KEY (FldDNo));
Dept_LocationsTableCREATE TABLE Dept_Locations(FldDNo NUMBER NOT NULL,FldLocation VARCHAR2(30) NOT NULL,CONSTRAINT "DEPTLOC_PK" PRIMARY KEY (FldDNo, FldLocation),CONSTRAINT fk_departFOREIGN KEY (FldDNo)REFERENCES Department(FldDNo));Employee TableCREATE TABLE Employee(FldFname VARCHAR2(30) NOT NULL,FldMinit VARCHAR2(1) NOT NULL,FldLname VARCHAR2(30) NOT NULL,FldSsn Number NOT NULL,FldBDate Date NOT NULL ,
FldAddress VARCHAR2(50) NOT NULL ,FldSex VARCHAR2(1) NOT NULL ,FldSalary Number NOT NULL ,FldSuper_ssn Number NULL,FldDNo Number NOT NULL,CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY (FldSsn),CONSTRAINT fk_departmentFOREIGN KEY (FldDNo)REFERENCES Department(FldDNo));Project TableCREATE TABLE Project(FldPnumber NUMBER NOT NULL,FldPname VARCHAR2(30) NOT NULL,FldPlocation VARCHAR2(30) NOT NULL,FldDNum NUMBER NOT NULL,CONSTRAINT "PROJ_PK" PRIMARY KEY (FldPnumber),CONSTRAINT fk_deptLocation
FOREIGN KEY (FldDNum, FldPlocation)REFERENCES Dept_Locations(FldDNo, FldLocation));Dependent TableCREATE TABLE Dependent(FldEssn NUMBER NOT NULL,FldDependent_Name VARCHAR2(30) NOT NULL,FldSex VARCHAR2(1) NOT NULL,FldBDate Date NOT NULL ,FldRelationship VARCHAR2(30) NOT NULL,CONSTRAINT "DEPEND_PK" PRIMARY KEY (FldEssn, FldDependent_Name),CONSTRAINT fk_empdependFOREIGN KEY (FldEssn)REFERENCES Employee(FldSsn));
desklib-logo
You’re reading a preview
Preview Documents

To View Complete Document

Click the button to download
Subscribe to our plans

Download This Document