Database Concepts and Management
Added on 2020-06-06
34 Pages6065 Words240 Views
|
|
|
Part 1 (A)Database designER diagram
Normalization processUndefined1NF2NF3NFDEPT_NAMEUniversityDepprtmentDEPT_IDDEPT_PRIMARY_LOCATIONDEPT_NAMEDEPT_IDDEPT_NAMEDEPT_ADDRESSDEPT_PRIMARY_LOCATIONDEPT_NAMEDEPT_PRIMARY_LOCATIONDEPT_SECONDARY_ADDRESSDEPT_ADDRESSDEPT_PRIMARY_LOCATIONDEPT_ADDRESSDEPT_EMAILDEPT_SECONDARY_ADDRESSDEPT_ADDRESSDEPT_SECONDARY_ADDRESSDEPT_PHONEDEPT_EMAILDEPT_SECONDARY_ADDRESSDEPT_EMAILDEPT_FAXDEPT_PHONEDEPT_EMAILDEPT_PHONEMODULENAMEDEPT_FAXDEPT_PHONEDEPT_FAXTITLEMODULENAMEDEPT_FAXMODULEIDCREDIT_HOURSTITLEModuleMODULENAMELEVELCREDIT_HOURSMODULEIDTITLECOURSENAMELEVELMODULENAMECREDIT_HOURSDURATIONCOURSENAMETITLELEVELCREDIT_HOURSDURATIONCREDIT_HOURSFK COURSEIDFACLT_NAMECREDIT_HOURSLEVELCOURSEIDFACLT_HOME_ADDRESSFacultyCOURSENAMEFACLT_OFFICE_LOCATIONFACLT_NAMECourseDURATIONFACLT_ROOM_NUMBERFACLT_HOME_ADDRESSCOURSEIDCREDIT_HOURSFACLT_FAXFACLT_OFFICE_LOCATIONCOURSENAMEFK DEPT_IDFACLT_EMAILFACLT_ROOM_NUMBERDURATIONFK FACLT_IDFACLT_HOME_PHONEFACLT_FAXCREDIT_HOURSFACLT_IDFACLT_MOBILEFACLT_EMAILFaculttyFACLT_NAMEGENDERFACLT_HOME_PHONEFACLT_IDFACLT_HOME_ADDRESSDATE_OF_BIRTHFACLT_MOBILEFACLT_NAMEFACLT_OFFICE_LOCATIONNEXT_OF_KIN_NAMEGENDERFACLT_HOME_ADDRESSFACLT_ROOM_NUMBERNEXT_OF_KIN_ADDRESSDATE_OF_BIRTHFACLT_OFFICE_LOCATIONFACLT_FAXNEXT_OF_KIN_RELATIONSHIPNEXT_OF_KIN_NAMEFACLT_ROOM_NUMBERFACLT_EMAILNEXT_OF_KIN_PHONENEXT_OF_KIN_ADDRESSFACLT_FAXFACLT_HOME_PHONEDATE_JOINEDNEXT_OF_KIN_RELATIONSHIPFACLT_EMAILFACLT_MOBILESALARYNEXT_OF_KIN_PHONEFACLT_HOME_PHONEGENDERFACLT_HOME_ADDRESSDATE_JOINEDFACLT_MOBILEDATE_OF_BIRTHFACLT_OFFICE_LOCATIONSALARYGENDERNEXT_OF_KIN_NAMEFACLT_ROOM_NUMBERFACLT_HOME_ADDRESSDATE_OF_BIRTHNEXT_OF_KIN_ADDRESSQUALI_DESCIPTIONFACLT_OFFICE_LOCATIONNEXT_OF_KIN_NAMENEXT_OF_KIN_RELATIONSHIP
DESCRIPTIONFACLT_ROOM_NUMBERNEXT_OF_KIN_ADDRESSNEXT_OF_KIN_PHONEHOURS_A_WEEKQUALI_DESCIPTIONNEXT_OF_KIN_RELATIONSHIPDATE_JOINEDFIRST_NAMEDESCRIPTIONNEXT_OF_KIN_PHONESALARYMIDDLE_NAMEHOURS_A_WEEKDATE_JOINEDFK QUALI_IDLAST_NAMEStudentSALARYSTUD_STREETFIRST_NAMEFK QUALI_IDSTUD_CITYMIDDLE_NAMEFACLT_HOME_ADDRESSSTUD_ZIPLAST_NAMEFACLT_OFFICE_LOCATIONSTUD_HOME_ADDRESSSTUD_STREETFACLT_HOME_ADDRESSFACLT_ROOM_NUMBERGENDERSTUD_CITYFACLT_OFFICE_LOCATIONQUALI_IDSTUD_EMAILSTUD_ZIPFACLT_ROOM_NUMBERQUALI_DESCIPTIONSTUD_PHONESTUD_HOME_ADDRESSQUALI_IDTEACHINGIDDATE_OF_BIRTHGENDERQUALI_DESCIPTIONDESCRIPTIONNEXT_OF_KIN_NAMESTUD_EMAILTEACHINGIDHOURS_A_WEEKNEXT_OF_KIN_ADDRESSSTUD_PHONEDESCRIPTIONFACLT_IDNEXT_OF_KIN_RELATIONSHIPDATE_OF_BIRTHHOURS_A_WEEKSTUD_IDNEXT_OF_KIN_PHONENEXT_OF_KIN_NAMEStudentFIRST_NAMESTUD_REGIONNEXT_OF_KIN_ADDRESSSTUD_IDMIDDLE_NAMENEXT_OF_KIN_RELATIONSHIPFIRST_NAMELAST_NAMENEXT_OF_KIN_PHONEMIDDLE_NAMESTUD_STREETSTUD_REGIONLAST_NAMESTUD_CITYSTUD_STREETSTUD_ZIPSTUD_CITYSTUD_HOME_ADDRESSSTUD_ZIPGENDERSTUD_HOME_ADDRESSFK FACLT_IDGENDERSTUD_EMAILFK FACLT_IDSTUD_PHONESTUD_EMAILDATE_OF_BIRTHSTUD_PHONENEXT_OF_KIN_NAMEDATE_OF_BIRTHNEXT_OF_KIN_ADDRESSNEXT_OF_KIN_NAMENEXT_OF_KIN_RELATIONSHIPNEXT_OF_KIN_ADDRESSNEXT_OF_KIN_PHONENEXT_OF_KIN_RELATIONSHIPSTUD_REGIONNEXT_OF_KIN_PHONEFK COURSEIDSTUD_REGION
(B)SQL script fileCREATE TABLE COURSES( COURSEID NUMBER(10), COURSENAME VARCHAR2(100 BYTE), DURATION NUMBER(10), CREDIT_HOURS NUMBER(10), DEPT_ID NUMBER(10), FACLT_ID NUMBER(10));CREATE TABLE DEPARTMENTS( DEPT_ID NUMBER(10), DEPT_NAME VARCHAR2(100 BYTE), DEPT_PRIMARY_LOCATION VARCHAR2(100 BYTE), DEPT_ADDRESS VARCHAR2(100 BYTE), DEPT_SECONDARY_ADDRESS VARCHAR2(100 BYTE), DEPT_EMAIL VARCHAR2(50 BYTE), DEPT_PHONE VARCHAR2(50 BYTE), DEPT_FAX VARCHAR2(50 BYTE), FACLT_ID NUMBER(10));CREATE TABLE FACULTY( FACLT_ID NUMBER(10), FACLT_NAME VARCHAR2(100 BYTE), FACLT_HOME_ADDRESS VARCHAR2(100 BYTE), FACLT_OFFICE_LOCATION VARCHAR2(100 BYTE), FACLT_ROOM_NUMBER NUMBER(10), FACLT_FAX VARCHAR2(50 BYTE), FACLT_EMAIL VARCHAR2(50 BYTE), FACLT_HOME_PHONE VARCHAR2(50 BYTE), FACLT_MOBILE VARCHAR2(50 BYTE), GENDER CHAR(1 BYTE), DATE_OF_BIRTH DATE, NEXT_OF_KIN_NAME VARCHAR2(100 BYTE),
NEXT_OF_KIN_ADDRESS VARCHAR2(100 BYTE), NEXT_OF_KIN_RELATIONSHIP VARCHAR2(100 BYTE), NEXT_OF_KIN_PHONE VARCHAR2(100 BYTE), DATE_JOINED DATE, SALARY NUMBER(12,2), QUALI_ID NUMBER(10));CREATE TABLE MODULES( MODULEID NUMBER(10), MODULENAME VARCHAR2(100 BYTE), TITLE VARCHAR2(100 BYTE), CREDIT_HOURS NUMBER(10), LEVELM VARCHAR2(2 BYTE), COURSEID NUMBER(10), FACLT_ID NUMBER(10), DEPT_ID NUMBER(10));CREATE TABLE QUALIFICATIONS( QUALI_ID NUMBER(10), QUALI_DESCIPTION VARCHAR2(100 BYTE));CREATE TABLE STUDENTS( STUD_ID NUMBER(10), FIRST_NAME VARCHAR2(100 BYTE), MIDDLE_NAME VARCHAR2(100 BYTE), LAST_NAME VARCHAR2(100 BYTE), STUD_STREET VARCHAR2(50 BYTE), STUD_CITY VARCHAR2(50 BYTE), STUD_ZIP VARCHAR2(50 BYTE), STUD_HOME_ADDRESS VARCHAR2(100 BYTE), GENDER CHAR(1 BYTE), FACLT_ID NUMBER(10), STUD_EMAIL VARCHAR2(50 BYTE), STUD_PHONE VARCHAR2(50 BYTE), DATE_OF_BIRTH DATE, NEXT_OF_KIN_NAME VARCHAR2(100 BYTE),
NEXT_OF_KIN_ADDRESS VARCHAR2(100 BYTE), NEXT_OF_KIN_RELATIONSHIP VARCHAR2(100 BYTE), NEXT_OF_KIN_PHONE VARCHAR2(50 BYTE), STUD_REGION VARCHAR2(100 BYTE), COURSEID NUMBER(10));CREATE TABLE TEACHING( TEACHINGID NUMBER(10), DESCRIPTION VARCHAR2(100 BYTE), HOURS_A_WEEK NUMBER(3), FACLT_ID NUMBER(10));Constraints:ALTER TABLE COURSES ADD ( PRIMARY KEY (COURSEID));ALTER TABLE DEPARTMENTS ADD ( PRIMARY KEY (DEPT_ID));ALTER TABLE FACULTY ADD ( PRIMARY KEY (FACLT_ID));ALTER TABLE QUALIFICATIONS ADD ( PRIMARY KEY (QUALI_ID));ALTER TABLE STUDENTS ADD ( PRIMARY KEY (STUD_ID));ALTER TABLE TEACHING ADD ( PRIMARY KEY (TEACHINGID));
ALTER TABLE COURSES ADD ( CONSTRAINT FK_DEPT FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS (DEPT_ID), CONSTRAINT FK_FACL FOREIGN KEY (FACLT_ID) REFERENCES FACULTY (FACLT_ID));ALTER TABLE DEPARTMENTS ADD ( CONSTRAINT FK_FACLT1 FOREIGN KEY (FACLT_ID) REFERENCES FACULTY (FACLT_ID));ALTER TABLE FACULTY ADD ( CONSTRAINT FK_WUIL FOREIGN KEY (QUALI_ID) REFERENCES QUALIFICATIONS (QUALI_ID));ALTER TABLE MODULES ADD ( CONSTRAINT FK_COUR FOREIGN KEY (COURSEID) REFERENCES COURSES (COURSEID), CONSTRAINT FK_FACLK2 FOREIGN KEY (FACLT_ID) REFERENCES FACULTY (FACLT_ID), CONSTRAINT FK_DEPT1 FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS (DEPT_ID));ALTER TABLE STUDENTS ADD ( CONSTRAINT FK_FACLT3 FOREIGN KEY (FACLT_ID) REFERENCES FACULTY (FACLT_ID), CONSTRAINT FK_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSES (COURSEID));ALTER TABLE TEACHING ADD ( CONSTRAINT FK_FCLY4 FOREIGN KEY (FACLT_ID) REFERENCES FACULTY (FACLT_ID));
Part 2:(A)Populate the database with some dataInsert into COURSES (COURSEID, COURSENAME, DURATION, CREDIT_HOURS, DEPT_ID, FACLT_ID) Values (1, 'MATH', 3, 60, 1, 1);Insert into COURSES (COURSEID, COURSENAME, DURATION, CREDIT_HOURS, DEPT_ID, FACLT_ID) Values (2, 'COMPUTER SCIENCE', 4, 20, 1, 1);Insert into COURSES (COURSEID, COURSENAME, DURATION, CREDIT_HOURS, DEPT_ID, FACLT_ID) Values (3, 'ENGLISH', 5, 30, 2, 2);Insert into COURSES (COURSEID, COURSENAME, DURATION, CREDIT_HOURS, DEPT_ID, FACLT_ID) Values (4, 'SOCIAL SCIENCE', 6, 40, 3, 3);Insert into COURSES (COURSEID, COURSENAME, DURATION, CREDIT_HOURS, DEPT_ID, FACLT_ID) Values (5, 'ARTS', 7, 50, 4, 2);COMMIT;Insert into DEPARTMENTS (DEPT_ID, DEPT_NAME, DEPT_PRIMARY_LOCATION, DEPT_ADDRESS, DEPT_SECONDARY_ADDRESS, DEPT_EMAIL, DEPT_PHONE, DEPT_FAX, FACLT_ID) Values (1, 'MATH', 'NY', 'NY', 'NY', 'math@usi.com', '444-222-5236', '4562-2356', 1);Insert into DEPARTMENTS
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents