work will be attached. this is due 16:30pm GMT, also required to use postgre SQL and also it has to work or work will be marked as 0
COMP5320 Assessment 3 Individual Assessment, No Group Work Permitted Scenario You and your group of collaborators were selected by the gym in Canterbury to modernize their IT system. After long discussions in the team and with the admin staff at the gym, you agreed on the following conceptual data model, and a set of assumptions and constraints agreed with the gym. Conceptual Data Model Assumptions and Constraints • A trainer may not give or be assigned to any classes yet • A member may not have booked any classes • A class may not yet have any members booked to attend it • A class is taught by exactly one trainer • A pay-per-use member may not yet have purchased a day pass. This relaxes the previously stated requirement that at least one day pass needs to be purchased, to keep some details simpler. Ferrae Thompson Relational Schema Based on the conceptual data model, the team decided to use a relational database and after a long back and forth, everyone agreed on the following relational schema as the logical data model: Primary keys are underlined, and foreign keys are in italic. AdminStaff (staffNo {PK}, name, dob, gender, phoneNo, startDate, leavingDate, role) Trainer (staffNo {PK}, name, dob, gender, phoneNo, startDate, leavingDate, speciality) Member (memberNo {PK}, name, dob, address, phoneNo, healthConditions) MonthlyContractMember (memberNo {PK, FK}, startDate, monthlyPrice, paymentDay, endDate) DayPass (memberNo {PK,FK}, startDate {PK}, numberDays, price) Class (staffNo {PK,FK}, onDate {PK}, onTime {PK}, level, maxNumber) Booking (staffNo {PK,FK}, onDate {PK,FK}, onTime {PK,FK}, memberNo {PK,FK}, bookingDate, bookingTime) No foreign key is allowed to be null, since none of the relevant relationships are optional. Constraints You also agreed on the following domain constraints: • name attributes need to be able to hold full names, which can be very short and very long • attributes with descriptive content, such as healthConditions, should avoid unnecessary length restrictions • gender in AdmingStaff and Trainer: {‘M’, ‘F’, ‘O’} (values are expected to be upper case) • dob: person must be born, i.e., a date in the past • role in AdminStaff: {‘manager’, ‘secretary’, ’assistant’, ‘facilities’} (values are expected to be lower case only) • leavingDate in AdminStaff and Trainer: leaving date must be after start date • endDate in MonthlyContractMembers: end date must be after start date • paymentDay in MonthlyContractMembers: a numeric value between 1 and 31 (inclusive) • level in Class: {‘beginner’, ‘intermediate’, ‘advanced’} (values are expected to be lower case only) • numberDays in DayPass: a numeric integer value, greater than zero • price in DayPass: a monetary value, thus, it needs a type that is prices enough to handle monetary values • montlyPrice in MontlyContract: a monetary value, thus, it needs a type that is prices enough to handle monetary values To ensure that all required data is available, you agreed on the following null-ability of attributes: • All Attributes should be NOT NULL, except for: o leavingDate in AdminStaff and Trainer o healthConditions in Members o endDate in MonthlyContractMembers • All foreign keys must be NOT NULL because a foreign key value is required in all cases in this model, which can be seen by the 1..1 in the relevant association multiplicities. TASK 1: Create Tables and Insert Data (40%) You were selected by your team to implement the relational schema in a physical data model using PostgreSQL. Thus, you will need to create the database tables and choose sensible data types for each column. Make sure to maintain entity integrity and referential integrity, too. Implement all the above constraints. The other rest of your team will start collecting data and prototyping a mobile frontend. While you won’t have to do these bits, you need to ensure that your team can rely on the above specification, e.g., in form of the relational schema to be correctly implemented in your database. This means, they need to be able to rely on all constraints being correct, the spelling of table and column names being as given, and that the data types that you chose will allow for sensible data to be added to the database in the future. For testing purposes, you agreed with your team to insert the data specified below into the tables, but no other data except this. In short: 1. You will need to create the tables as specified 2. You will need to add the data specified Please use the assessment3.sql file from Moodle and implement your tables using the sketched CREATE TABLE commands. Add the necessary queries in the file where indicated. AdminStaff staffNo name dob gender phoneNo startDate leavingDate role 1 Jill Joffries 1989-09-10 F +44 12312-234-656 2018-12-01 null manager 2 Fred Grimes 1994-02-01 M 0752343456 2020-03-01 null secretary Trainer staffNo name dob gender phoneNo startDate leavingDate speciality 3 Virginia Vair 1995-11-20 F 01227523423 2020-10-01 null older adults 4 Jim Adams 1990-12-31 M 0752243456 2021-09-01 null pilates 5 Mike Tylor 1974-07-03 M +44 (0) 71 234 4534 5653 2018-12-01 null personal training Member memberNo name dob address phoneNo healthConditions 1 Adam Twill 1955-03-12 15 Giles Lane, CT2 7NZ, Canterbury 0435-234-4354 issues with joints 2 Sue Adams 2004-02-25 27 Cherry Street, CT4 7NF 0752243451 null 3 John Smith 2002-08-14 4 Harkness Drive, Canterbury (0) 12 234 4534 5653 null MonthlyContractMember memberNo startDate monthlyPrice paymentDay endDate 1 2022-01-01 £35.99 5 null 2 2022-01-01 £12.99 31 null DayPass memberNo startDate numberDays price 3 2022-01-01 5 £9.99 3 2022-02-01 5 £9.99 3 2022-03-01 5 £9.99 Class staffNo onDate onTime level maxNumber 3 2022-03-02 06:30:00 intermediate 10 3 2022-03-02 07:30:00 advanced 10 4 2022-03-02 10:00:00 beginner 5 4 2022-03-02 11:00:00 beginner 5 5 2022-03-02 11:00:00 advanced 2 5 2022-03-10 09:00:00 advanced 2 Booking staffNo onDate onTime memberNo bookingDate bookingTime 3 2022-03-02 06:30:00 1 2022-02-28 20:23:00 3 2022-03-02 06:30:00 3 2022-03-01 10:41:00 4 2022-03-02 11:00:00 2 2022-03-01 12:48:00 5 2022-03-02 11:00:00 3 2022-03-01 13:23:00 TASK 2: Query the Database (10% Each query) For each subtask, create a single PostgreSQL query. For queries that return information, return only the data specified and use only the information provided in the question. The queries must utilize the information given but cannot hard code ids or other information that is not explicitly given. Ensure the queries will work with any future data, not just the data given. Your queries will be tested with data not provided here and need to be general enough to anticipate sensible data. Your submission needs to contain the queries in the same order as listed here. 2.1) List the date and time of classes by Virginia Vair that have bookings. 2.2) List the names of all staff members that joined the company before 2020. 2.3) List the years and month, in which there are members with day passes, and show for each year-month combination how many day passes were bought. 2.4) List the names of the trainers, the levels of their classes, and the time and date for the classes that do not currently have a booking. 2.5) Show the names of the trainers with at least two classes at the same level. Include the level and the number of classes (as classCount) in the output table. Order the table from highest classCount to lowest. 2.6) Mike Tylor is on holidays from March 9th to March 11th. Jim Adams will cover for him. Update the database to reflect the change. For simplicity, you can assume that there are no bookings for Mike’s classes yet, so, it's sufficient to reassign his classes to Jim. Assessment Submission and Marking The assessment needs to be submitted via Moodle. The deadline is given there. Submit your solution by modifying the provided assessment3.sql file. Do not use Word, Pages, LibreOffice, Google Docs or similar “rich text editors” to prepare you submission. Instead use VS Code, Nodepad++, Notepad, Atom, or another IDE or text editor that handle plain text. In your submission file, you should provide: • your name and login as indicated near the top of the file, • the PostgreSQL code to create the tables (in the specified order), • the PostgreSQL code to insert the data (in the order same order the data is given here), • and the PostgreSQL code for the queries. • All queries must be immediately executable, and the complete file as submitted, should have been test by you. You are expected to use PostgreSQL. Other SQL dialects are not accepted and may result in 0 marks. Code that does not work will get 0 marks. Spelling mistakes and similar inaccuracies with respect to the specification given above will be considered mistakes, and you may lose marks. Any non-executable text must be in comments using “--”. Do not change existing comments, except where explicitly requested. Comments are used in the automated marking system. If you comment out queries, the system assumes you don’t want them to be considered for marking. Late submissions will get 0 marks. The use of tools or programs that generate SQL is not permitted and considered a form of plagiarism. All submissions will be marked automatically. To test your submission, you can use the interface from class 4: https://www.cs.kent.ac.uk/people/staff/sm951/co532/class4.html For questions, you can email me at
[email protected]. Plagiarism and Duplication of Material Senate has agreed the following definition of plagiarism: “Plagiarism is the act of repeating the ideas or discoveries of another as one's own. To copy sentences, phrases or even striking expressions without acknowledgement in a manner that may deceive the reader as to the source is plagiarism; to paraphrase in a manner that may deceive the reader is likewise plagiarism. Where such copying or close paraphrase has occurred the mere mention of the source in