Using the provided data description in section 2.1.1 and considering the nature of that data to direct your thinking, complete these tasks:

a) Propose a relational database design for the case described below in section 2.1.1 on YouBeeYou College. Requirements for the relational database design:

Present your relational database design as a logical ERD.

Your design must show table names, column names, primary keys, relationships, and foreign keys pertinent to your design.

Your design must be in 3NF. In other words, following good database design principle, the ERD should not contain redundant entity sets, relationships or attributes.

If you need to make any assumptions, state them clearly in your answer.

b) Discuss methods of controlling data integrity for the following relations based on your logical ERD

i. The relation which would store all the data for time slots.

ii. The relation which would store all the data for computers.

c) Discuss two situations where denormalization could be used to improve performance in your model when converting your proposed logical design (in part a) above) to physical tables. Be as specific as possible in your discussion. Include segments of modified diagrams to aid your discussion.

Requirements:

For each situation explain how your denormalised design would improve performance of the physical model.

For each situation explain how your denormalised design may introduce trades-off for effectiveness.

The YouBeeYou College is a (fictitious) tertiary institution which offers courses in design, animation and technology for its students. There has been a recent growth in enrolment numbers and the college has hired

Page 2 of 6

you to create a class scheduling database. After several interviews with the board of management, the following initial business rules, entities and attributes have been identified.

The College uses two buildings. Most of the rooms in these buildings are used as teaching spaces, either in the form of a lab or a classroom. These are the only room types to be incorporated into the system. A room cannot be both a classroom and a lab. The building ID and room no is used to identify a room. Each room has a capacity. Different types of smartboards are used in the classrooms. This board type must be recorded in the database.

Each lab is furnished with multiple desktop computers. The type of computer and its disk capacity, as well as processor speed, is to be recorded in the database. Classrooms do not have computers.

In addition to smartboards/computers, each room may have other equipment. These could be projectors, recording hardware, digital cameras, presentation clickers, tablet devices etc. The equipment type and description are stored for this equipment.

The instructors at the College have a rank (Lecturer, Senior Lecturer, Professor, Tutor etc.). The names and addresses together with office extension number for each instructor must be recorded.

An instructor will teach many different courses. Each course has an allocated number of credits, together with a course ID and course description. A course can have one, none, or many prerequisites. Due to high enrolment numbers, a course is taught in many sections. Because of this several different instructors may teach the same course. Each section of a course has an enrolment limit. In each semester an instructor teaches one, none, or many sections of a course.

Each instructor has preferred time slots (day of the week, start time, and end time) for teaching. Based on these time slots a schedule for teaching the sections of each course in specified rooms is created for each semester of each year.