Question

DATABASE SYSTEMS Project INDIVIDUAL WORK DELIVERABLE #: SUBMISSION DATE No Group Work Allowed April 8 Introduction to Coursework You have been approached by a University for the design and implementation of a relational database system that will provide information on the courses it offers, the academic departments that run the courses, the academic staff and the enrolled students. The system will be used mainly by the students and the academic staff. The requirement collection and analysis phase of the database design process provided the following data requirements for the University Database system Each department runs a number of courses. The university provides a set of modules used in different courses. Each course uses a number of modules but not every module is used. A course is assigned a unique course code and a module is identified by a unique module code. A module can be used in one course only, but can be studied by many students. In addition to the module code each module unique title, start date, end date, texts (books), and assessment scheme (ie. coursework and exam marks percentages) are also stored Each course is managed by a member of academic staff, and each module is coordinated by a member of academic staff also. The database should also store each course unique title, and duration (in years). A student can enrol in one course at a time. Once enrolled a student is assigned a unique matriculation number. To complete a course, each student must undertake and pass all the required modules in his/her course. This requires that the database store the performance (pass or fail) of each student i every module. Additional data stored on each student includes student name (first and last), address (town, street, and post code), date-of-birth, sex, and financial loan. For emergency purposes the database stores the name (not composite), address (not composite), phone, and relationship of each student next-of-kin. None of the next-of-kin's attributes is unique Assume that every next-of-kin is a next-of-kin of one student only Each department is managed by a member of academic staff. The database should record the date he/she started managing the department. Each department has a name, phone number, fax number, and location (e.g. E Block). Each department employs many members of academic staff. A member of academic staff can be the leader (i.e. manager) of at most one course, but can be the coordinator of more than one module. A member of academic staff may not be assigned any of the above mentioned roles (coordinator, course leader, department manager) All members of academic staff teach modules. Every member of academic staff teaches one or more modules, and a module may be taught by more than one member of academic staff. The database should record the number of hours per week a member of academic staff spend teaching cach module. Each member of academic staff is identified by a unique staff number. All members of staff and students have unique computer network user ID numbers. Additional data stored on each member of academic staff includes name (first and last), phone extension number, office number, sex, salary, post (lecturer, or senior lecturer, or Professor, etc.), qualifications, and address (not composite). A member of academic staff work for one department only Part 1Design the Database I. Create an Entity-Relationship (ER) model of the data requirements for the University Darabase case study using the UML notation Note: if necessary, use the additional concepts of the Enhanced EntityRelationship (EER) model. State any assumptions necessary to support your design. 2 Derive relational schema from your ER model that represents the entities and relationships. Identify primary, altemate and foreign keys. Note: use the following notation to describe your relational schema, as shown in the example of a Staff relation given below Staff (staffNo, fName, IName, address, NIN, sex, DOB, deptNo) Primary Key staffNo Alternate Key IName, DOB Alternate Key NIN Foreign Key deptNo references Department(deptNo) On Delete No Action On Update Cascade 3. Use the technique of nomalization to validate the structure of your relational schema. Demonstrate that each of your relations is in third nomal form 3NF) by displaying the functional dependencies between attributes in cach relation. Note, if any of your relations are not in 3NF, this may indicate that your ER model is structurally inconrect or that you have introduced erors in the process of deriving relations from your model. 4.To further demonstrate your knowledge of normalization, assume that a proposed (badly structured) relation for the University Database database has the following structure. sex moduleTitle module startDate matricNo 005021 fatNo F001 F001 Mcleod, A Smith, J Owen, M Smith, J Mcleod, A Smith, J 27/09/01 M Sotware Dew. 01/10/01 27/09/01 01/10/01 27/09/01 27/09/01 6 lady Lane, Paisley 6 lady Lane, Paisley 28 New Str, Paisley 28 New Str, Paisley 6 lady Lane, Paisley 6 lady Lane, Paisley BITS Pass Pass 01/4670 01/4765 005021 0014847 Pass Pass Fail FO03 F001 F001 M FDBS ldentify the functional dependencies represented in this relation and demonstrate the process of normalizing this relation into 3NF relations

Answer 1

(II).(1) Staff(Staff_No,FName,LName,address,Sex,Qualification,Post,Salary,User_id,dept_No,No_of_hrs)

STA FA Sm Coordinat DEPA RTMENT Lotastion

Alternate Key User_id

Foreign Key dept_No references Department(dept_No) On Delete No Action On Update Cascade

(2) Student(User_id,Matriculation_No,FName,LName,DOB,Sex,Financial_Loan,Street,Town,Post_Code,Performance)

Primary Key Matriculation_No

Alternake Key User_id

Foreign Key Course_Code references Course(Course_Code) On Delete No Action On Update Cascade

(3) Next_Of_Kin(Matriculation_No,Name,Address,Phone,Relationship)

Primary Key Matriculation_No

Foreign Key Matriculation_No references Student(Matriculation_No) On Delete No Action On Update Cascade

(4) Department(dept_No,Name,Phone_no,Fax_no,Location)

Primary Key dept_No

(5)Course(Course_Code,Title,Duration,module_Code)

Primary Key Course_Code_

Foreign Key module_Code references Module(module_Code) On Delete No Action On Update Cascade

(6) Module(module_Code,title,Start_Date,end_Date,Books,Exam_Percantage,Course_work_marks)

Primary Key module_Code