School of Science — ISYS3412 Practical Database Concepts Assessment 1: Database Design Project Assessment Type: PDF Word limit: N/A (see instructions) Due date: 11:59PM (AEST), Sunday 23 August 25% of your overall grade Overview The objective of this assignment is to measure your understanding of the basic concepts in the relational database model and using entity-relationship model for database design. The assessment is in two parts, split into four tasks which cover Basic ER Modelling, Basic Relational Modelling and Basic SQL Programming. The tasks are as follows: Part A: Entity-Relationship Modelling (70 points) 1. Using Entity-Relationship Model, design a conceptual model to represent a set of data requirements given in the specifications and present the design to a high standard using UML notation through the diagramming tool LucidChart. 2. Model the activities of a small business and present the model as an Entity-Relationship (ER) diagram. Then modify this ER diagram based on additional client requirements. 3. Map an ER diagram into a relational database schema, showing every step of the mapping. Part B: Relational Database Model (30 Marks) 4. Answer a series of short questions about a Relational Database model. To complete this assessment, you must be familiar with LucidChart, which is covered during the Week 3 – 4 activities. Assessment Criteria This assessment will measure your ability to: • Accurately interpret business rules (data requirements) provided in a specification document; • Then, represent them using Entity-Relationship Model; • Identify additional requirements a client stated later and modify an existing Entity Relationship Model to accommodate such new requirements; • Convert an Entity-Relationship Model to Relational Database Schema. • Application of integrity constraints in a Relational Database Schema. Page 1 of 10 Course Learning Outcomes This assessment is relevant to the following Course Learning Outcomes: CLO 1: describe the underlying theoretical basis of the relational database model and apply the theories into practice; CLO 2: explain the main concepts for data modelling and characteristics of database systems; CLO 3: develop a sound database design using conceptual modelling mechanisms such as entity-relationship diagrams; CLO 4: develop a database based on a sound database design; Page 2 of 10 Part A Entity-Relationship Modelling (70 points) Task 1: Designing an Entity-Relationship Model • You have just been employed as a database designer in a well-established software development firm. Your first job is to design and implement a database system for a university enrollment management system. The following information has been gathered after analysing the current practices of the university. • The system records information about student enrollments into courses. Students have a unique studentID and also have a name, date of birth (DOB), and a gender. • Students could be either local or international. Local students also have a tax file number and may have a HEC debt (with a record of the amount owing), while international students have a visa number. • Courses have a unique courseID as well as a name, credit points and a fee. Courses may have prerequisite courses that must be completed beforehand. • Courses can run in multiple years and semesters as a course offering, though courses are defined beforehand (and their details do not change from semester to semester). • Student enrollments record the status of the enrollment (inProgress or completed) as well as the grade the student achieved for a particular year and semester. • Student may be enrolled multiple times in the same course over different semesters (ie if they fail and have to repeat the course). • Each semester, there is one lecturer who presents the course material. Lecturers can teach more than one course. • Each course can also have multiple tutors (who can also teach multiple courses). • Both lecturers and tutors have a unique StaffID, a name and can have multiple qualifications. • Each qualification has a name and the year it was achieved by the Staff member. Page 3 of 10 Based on the information you gathered, model the activities in your client’s business and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes; relationships and their cardinality and participation constraints. If you cannot represent any of the information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model. You must use UML notation and the diagramming tool LucidChart to draw your diagram. Your diagram must be drawn to a high standard with minimal clutter. You are not required to map the ER model to relational model. A special note: This is an open-ended question with many different models can be derived. Your model is assessed based on how accurately it represents business rules described above. When designing your model, you must justify your design decisions and identify the strengths and limitations of your design. Page 4 of 10 Task 2: Designing and refining an Entity-Relationship Model A cruise liner wants a simple database to track passenger details. They describe the key elements of their requirements in the following points: • Passengers book voyages. Each passenger has a firstname, lastname, date of birth, and passport number. • Each voyage has a unique code to identify it. • When booking a voyage, customers provide a credit card no, expiry date and is allocated a cabin number. • Each voyage is on board a particular ship. Each ship has a distinct name and has a capacity. • Each voyage departs and arrives from ports on specific dates (and times), with each port identified by the country and city. Each country could have multiple ports in different cities, with each port having a certain number of piers for docking. • A ship can have several port stops during the voyage. The order can be determined by the date and time of arrival/departure at each port. Based on the information you gathered, model the activities in your client’s business and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes; relationships and their cardinality and participation constraints. After presenting your ER model to the management, they pointed out that your model lacks the ability to capture all requirements of the operation. In particular, they pointed out the following shortcomings: 1. They want to be able to differentiate between passengers on board the ship as a guest and passengers who actually make the bookings. Both have the same details (firstname, lastname, date of birth and passport number). The guests of a customer could vary between different bookings, but are limited to a maximum of three. 2. They also want to be able to keep track of bookings and cabins separately. Bookings have a date and a unique booking number. Bookings are for a cabin. The system records details of each cabin, including the room number, room type and capacity. Note that the room number could be the same for different voyages (eg room 1001), with potentially different room types and capacity on different voyages. 3. Cabins are allocated to specific voyages. Each voyage has a unique code to identify it. 4. The system also needs to record employee details working on each voyage, specifically the employee number and name as well as details of the specific
(supervisor) employee that they (subordinates) report to. Draw a modified ER diagram to accommodate these additional requirements. Note: Your answer to this question should include TWO complete ER diagrams. Page 5 of 10 Task 3: Mapping an ER Model to a Relational Database Schema Consider the following ER diagram, which models an online library. Map this ER diagram into a relational database schema. Show every step of the mapping. No marks are awarded to the final schema if you do not show the partially built schema at the end of each step. Indicate the primary key (underlined) and foreign key (with an asterisk) in each relation. Librarians and Members are the only users of the system. Page 6 of 10 Part B (30 Marks) Task 4: Relational Database Model This question has been adopted from Fundamentals of Database Systems, Elmasri and Navathe. (Question 5.11). A relational database schema and an instance of this schema are given below. Most of the attribute names are self-explanatory. Super_SSN refers to corresponding employee’s supervisor’s SSN (Social Security Number). This example is based on US system, assume it is similar to Australian Tax File Number. Arrows indicate foreign keys and the corresponding attributes in parent relation. In the case of Super_SSN, the parent relation is the Employee relation itself (self-referencing). Page 7 of 10 1. On the date 2020-02-01 employee Franklin T Wong reaches retirement age and is succeeded by Ramesh as new manager of the research department. Franklin’s employee record is to be deleted from the database and Ramesh’s details updated accordingly. As part of the new role Ramesh’s pay is to be updated to match Franklin’s former pay (this update must be performed dynamically, rather than hard coding the amount) Identify each of the SQL commands (in the correct order) that must be run to update all the information to reflect this change. You are not allowed to use a cascade operation/constraint or disable foreign key constraints. Page 9 of Referencing guidelines Use Harvard referencing style for this assessment. You must acknowledge all the courses of information you have used in your assessments. Refer to the RMITEasy Cite referencing tool to see examples and tips on howto reference in theappropriated style. You can also refer to the library referencing page for more tools such as EndNote, referencing tutorials and referencing guides for printing. Submission You should submit one PDF document with all answers together. You may use LucidChart to work on Part 1 of your assignment. You may use Word or any other word processor to compile your submission. At the end, convert it into PDF format. Do not submit Word files. if that option is not available on your system there are free pdf converters online you can utilise. e.g. http://convertonlinefree.com/ Submit to the assessment page in canvas by the due date. Academic integrity and plagiarism Academic integrity is about honest presentation of your academic work. It means acknowledging the work All code or other material that is not original must be fully credited. That is, any material that is copied or derived from another source must be clearly identified as such and the original author must be identified. Sometimes students assist each other with an assignment, but end up working together too closely, so that the students’ separate solutions have significant parts in common; unless the solutions were developed independently, they are regarded as plagiarised. Plagiarism is a very serious offence. Any submissions determined to be a result of plagiarism willbe deemed as an academic misconduct and harsh penalties apply. It is also an offence for students to allow their work to be plagiarised by another student. You should familiarize yourself with the university website for Academic Integrity Policy, Procedures and Guidelines: https://www.rmit.edu.au/students/student-essentials/rights-and- responsibilities/academic-integrity All work is to be done individually and plagiarism of any form will be dealt with according to the RMIT plagiarism policy. Penalties for late submissions Late submissions of assignments will be penalised as follows. For 1 to 5 days late, a penalty of 10% (i.e. 10% out of total marks, not 10% out of your marks) per day. For assignments more than 5 days late, 100% penalty applies. Special Consideration If unexpected circumstances affect your ability to complete the assignment you can apply for special consideration. If you seek a short extension, you can directly contact the lecturer. For longer extensions, you must follow instructions provided at: http://www1.rmit.edu.au/students/specialconsideration Assessment declaration When you submit work electronically, you agree to the assessment declaration. Page 10 of 10
