A major faculty consisting of many departments at the Whangarei Institute of Technology (WIT) has approached you for the design and implementation of a relational database system. The requirements phase of the database development cycle identified the following data requirements for the faculty's proposed database system. WIT offers many programmes (e.g., Bachelor of Science, Bachelor of Information Technology, Bachelor of Software Engineering, Bachelor of Computer Science). The information to be stored for a programme is: programme code, level, points, duration (in years), campus, and semester. Each department runs several programmes. Each programme uses several courses (e.g., Programming I, Database System Design), but not every course is offered every semester/year to students. A course can be offered in more than one programme, and a programme may offer many courses. The information to be stored about a course is: course code, course name, and course credits. Each programme is managed (e.g., a Programme Director) by a member of the academic staff, and each course is coordinated by a member of the academic staff also. A student can enrol in one programme only at a time. Once enrolled a student is assigned a unique student identification number. To complete a programme, each student must undertake and pass all the required courses in the programme. This requires that the database store the performance (pass or fail) of each student in every programme. Additional data stored on each student includes student name (first and last), address (town, street, and postcode), date of birth, gender, and student financial loan (Yes or No). For emergency purposes, the database stores the last name, first name, address, phone, and relationship of each student's next-of-kin. Assume that every next-of-kin is a next-of-kin of one student only. Each department is led by an academic staff member (e.g., the Head of the Department). The database should record the date the staff member started the role as Head of the department. Each department has a name, code, phone number, and location (e.g., Z Building). Each department employs many members of academic staff. An academic staff member works for one department only. An academic staff member can be the director of at most one programme but can be the coordinator of more than one course. An academic staff member may not be assigned any of the above mentioned roles (such as Course Coordinator, Programme Director, Head of the Department). All members of the academic staff teach courses. Every member of the academic staff may teach zero, one or more courses, and a course may be taught by more than one academic staff member. The database should record the number of hours an academic staff member spends teaching each course per week. The information to be stored for an academic staff member is: staff number, name (first and last), employment start date, phone extension number, office number, gender, salary, position (e.g. Lecturer, Senior Lecturer, Professor), and highest qualification.

A major faculty consisting of many departments at the Whangarei Institute of Technology (WIT) has approached you for the design and implementation of a relational database system. The requirements phase of the database development cycle identified the following data requirements for the faculty's proposed database system. WIT offers many programmes (e.g., Bachelor of Science, Bachelor of Information Technology, Bachelor of Software Engineering, Bachelor of Computer Science). The information to be stored for a programme is: programme code, level, points, duration (in years), campus, and semester. Each department runs several programmes. Each programme uses several courses (e.g., Programming I, Database System Design), but not every course is offered every semester/year to students. A course can be offered in more than one programme, and a programme may offer many courses. The information to be stored about a course is: course code, course name, and course credits. Each programme is managed (e.g., a Programme Director) by a member of the academic staff, and each course is coordinated by a member of the academic staff also. A student can enrol in one programme only at a time. Once enrolled a student is assigned a unique student identification number. To complete a programme, each student must undertake and pass all the required courses in the programme. This requires that the database store the performance (pass or fail) of each student in every programme. Additional data stored on each student includes student name (first and last), address (town, street, and postcode), date of birth, gender, and student financial loan (Yes or No). For emergency purposes, the database stores the last name, first name, address, phone, and relationship of each student's next-of-kin. Assume that every next-of-kin is a next-of-kin of one student only. Each department is led by an academic staff member (e.g., the Head of the Department). The database should record the date the staff member started the role as Head of the department. Each department has a name, code, phone number, and location (e.g., Z Building). Each department employs many members of academic staff. An academic staff member works for one department only. An academic staff member can be the director of at most one programme but can be the coordinator of more than one course. An academic staff member may not be assigned any of the above mentioned roles (such as Course Coordinator, Programme Director, Head of the Department). All members of the academic staff teach courses. Every member of the academic staff may teach zero, one or more courses, and a course may be taught by more than one academic staff member. The database should record the number of hours an academic staff member spends teaching each course per week. The information to be stored for an academic staff member is: staff number, name (first and last), employment start date, phone extension number, office number, gender, salary, position (e.g. Lecturer, Senior Lecturer, Professor), and highest qualification.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter9: Database Design

Section: Chapter Questions

Problem 7P

See similar textbooks