PART II: CASE STUDIES Business scenario for the College Accommodation Management System The director of the Western College Accommodation Office requires you to design a database to assist with the administration of The College Residences. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the Western College Accommodation Office database.

PART II: CASE STUDIES

The business scenario for the College Accommodation Management System

The director of the Western College Accommodation Office requires you to design a database to assist with the administration of The College Residences. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the Western College Accommodation Office database.

Data requirements

The data stored for each Student includes name (first name, family name), home address (street, town, postcode), contact phone number, email address, date of birth, gender, category (university preparation, undergraduate, postgraduate), and status (waiting, resident).

The accommodation available is organised into residences, each of which has a unique name, address, telephone number, and a Hall Manager who supervises the operation of the residence. Each residence contains several rooms.

Each room is assigned a unique room number and the cost of occupancy for a semester is recorded for each room. The room number uniquely identifies each room in all residences controlled by the Accommodation Office and is used when renting a room to a student.

Each room is inspected on a random basis; data held about each Inspection includes the date of inspection, name of the staff member who carried out the inspection, inspection outcome (satisfactory, unsatisfactory) and general comments.

At the start of each semester, each student accepted into a residence is assigned a room for that semester — this constitutes the Occupancy record. It is necessary to maintain an historical record of occupancies, that is, to be able to tell who occupied a room between specified dates.

Each semester, every resident student is issued with an Invoice related to their occupancy of a room, detailing the date the invoice was issued, and cost for the semester. Recording payment of the invoice is NOT part of this system.

Using the information supplied, represent the data requirements for your logical database schema in a single entity-relationship diagram (ER diagram). Your ER diagram should:

identify the main entity types:

identify attributes and associate them with entity or relationship types; represent each attribute in your ER diagram
identify the main relationship types between the entity types identified in part (a) and represent each relationship on your ER diagram
show the primary and foreign key attributes for each entity, and

show the multiplicity constraints for each relationship identified in part (c) using either crow’s foot or UML notation.

Note: ER diagrams can be drawn by using a variety of tools (eg, Microsoft Visio/Lucid chart/draw.io/Microsoft Word). With some applications, you may wish to take screenshots of your ER diagrams and insert them into your Word document.

Business scenario for the Youth League Management System

The local city Youth League needs a database system to help track children who sign up to play soccer. Data needs to be kept on each team, the children who will play on each team, and parent details. Also, data needs to be maintained on the coaches for each team and matches.

You need to store the Team ID, Team name, Player ID, Player first name, Player last name, and Player age for all players. Each team may or may not have a player. A player must have a team and should belong to only one team. You also need to store the Coach ID, First name of the coach, Last name of the coach, Home phone number of the coach.

A Team may have many coaches or may not have a coach. A coach must have only one team assigned to him or her. For each parent, the Parent’s ID, their Last name, First name, Home phone number and Home address need to be kept in the system.

A player may have more than one parent. A parent may have only one player. Teams play matches. For each match we need to keep track of the following: Match ID, the Date on which the game is played, the Result of the match, Host team ID (HID) and Guest team ID (GID).

Normalise these relations to 3rd normal form, ensuring that the resulting relations are dependency preserving. Explain each step clearly along with any assumptions you may have made.

SUBMISSION GUIDELINES

You are to submit your portfolio via the Assessment 1A: Portfolio A link on vUWS.
Name the Word file to be submitted in the following form: StudentIDFirstPortfolioA, eg 18325845RichardPortfolioA.

MARKING GUIDE

Criteria	Fail 0–49%	Pass 50–64%	Credit 65–74%	Distinction 75–84%	High distinction 85–100%
Demonstrates knowledge of the roles people play (20 marks)	Table not completed or the answer does not show understanding of all of the following: number of jobs date of search highest salary of the job key requirements of the role, with more than three cell values that do not relate to the corresponding item. Does not identify the role that is of personal interest and/or does not justify their choice clearly.	All components of the table completed except that three cell values do not relate to the corresponding item. Identifies the role that is of personal interest and provides justification of their choice.	All components of the table completed except that two cell values do not relate to the corresponding item. Identifies the role that is of personal interest and provides justification of their choice.	All components of the table completed except that one cell value does not relate to the corresponding item. Identifies the role that is of personal interest and provides justification of their choice.	All components of the table are completed. Identifies the role that is of personal interest and provides justification of their choice.
Identifies advantages and disadvantages of a database system (10 marks)	Does not describe an advantage and/or a disadvantage of a DBMS. Does not describe a relevant scenario.	Identifies an advantage and a disadvantage of a DBMS, and describes a scenario but does not explain how the feature is an advantage or the feature is not depicted well in the scenario.	Identifies and describes a key advantage and a disadvantage of a DBMS, and describes a scenario explaining how the feature is an advantage.	Identifies and describes a key advantage and a disadvantage of a DBMS, and describes a scenario explaining convincingly how the feature is indeed an advantage.	Identifies and describes a key advantage and a disadvantage of a DBMS, and describes a scenario explaining convincingly and succinctly how the feature is indeed an advantage.
Differentiate between RDBMS and file-based (traditional) systems (15 marks)	Does not describe components of a DBMS and how they relate to one another and/or does not differentiate between file- based systems and RDBMS.	Describes some components of a DBMS, but does not describe the relationship between the components clearly. Describes some features of file- based systems and RDBMS but does not explain the distinguishing features clearly.	Describes the components of a DBMS without the relationship between the components being clearly portrayed, and explains at least two points of difference between file-based systems and RDBMS.	Describes the components of a DBMS and the relationship between the components clearly, and explains at least three points of difference between file-based systems and RDBMS.	Describes the components of a DBMS and the relationship between the components clearly and effectively, and explains at least four points of difference between file-based systems and RDBMS.

Criteria	Fail 0–49%	Pass 50–64%	Credit 65–74%	Distinction 75–84%	High distinction 85–100%
Apply basic skills in database modelling including ER diagrams (35 marks)	The logical database schema includes some entities and attributes but does not model the database effectively to solve the given problem. Most primary keys, foreign keys and constraints are not identified. The model is not presented using correct notation.	The logical database schema includes some of the essential entities with some attributes to model the solution database with meaningful relationships. Most primary keys, foreign keys and constraints are identified correctly. The model is presented using correct notation.	The logical database schema includes the essential entities with sufficient attributes to model the solution database with meaningful relationships. Primary keys, foreign keys and constraints are identified correctly. The model is presented using correct notation and is easy to comprehend.	The logical database schema includes most of the enhanced entities with their required attributes in an ER diagram that models the solution database with meaningful relationships. Primary keys, foreign keys and constraints are identified correctly. The model is designed effectively, is presented using correct notation and is easy to comprehend.	The logical database schema includes all the enhanced entities with their required attributes in an ER diagram that models the solution database with meaningful relationships. Primary keys, foreign keys and constraints are identified correctly. The model is designed efficiently, is presented using correct notation and is easy to comprehend.
Normalisation in RDBMS (20 marks)	The database is unnormalised and/or no steps clearly mentioned.	The tables are normalised only to 1NF mentioning all steps, ensuring the resulting relations are dependency preserving.	One table is normalised to 2NF and all others to 1 NF mentioning all steps, ensuring the resulting relations are dependency preserving.	One table is normalised to 3NF and all others to 2 NF mentioning all steps, ensuring the resulting relations are dependency preserving.	The database is normalised to 3NF ensuring the resulting relations are dependency preserving.
Total marks: 100

Related posts: