CS27020 Assignment – AberTutors This assignment counts for 30% of the marks for CS27020 Released: 18/10/19, Deadline: 8/11/19, 1pm, submission via BlackBoard. Introduction AberTutors is a fictitious agency that puts students in touch with tutors. They need to have a system that can help them keep track of who can teach which topics in which area. They don’t keep a record of who is tutoring who - they’re just a contacts agency. Tip: ensure you read all the assignment before starting. (Careful thought in part 1 can really help with part 6.) Assignment 1. Consider the problem of data storage for tutoring organisation: you have been approached to build a database to help Aber Tutoring. Currently they have a book with a page per subject listing tutors and their details, and a list of students. Maths Jackie 01970666543, Aberystwyth Jeff
[email protected], Machynlleth/Talybont/Bow Street Cymraeg Ahmed 07723456788, Borth/Aberystwyth/Bow Street/Llandre Dafydd 07845333444 Machynlleth English Jackie 01970666543, Aberystwyth Peter
[email protected] Bow Street Art Ahmed 07723 456788, Borth/Aberystwyth/Bow Street/Talybont Brian
[email protected], Aberystwyth/Llanrhystud Science Jeff
[email protected], Machynlleth/Talybont/Bow Street The student list looks like this: Name Contact Details Subjects wanted Location Elin Davies
[email protected] Maths,Science Machynlleth Steve Smith
[email protected] English Llanrhystud ... Write down the elements that you would include in the database. This should be in UNF (e.g “one big table”). This should include an indication of data types. [20 marks] 2. From your data model identify functional dependencies between attributes. [10 marks] 3. Bring the un-normalised structures to 3rd normal form following the steps shown in lectures (see lectures on normalisation). Each normalisation step should be documented. Perform and justify any mergers of the relations that result from normalisation. [15 marks] 4. Draw an ER diagram of this database structure. [10 marks] 5. Implement your solution as a PostgreSQL database. Populate this with reasonable fictitious data. Show that you have done this using the PostgreSQL “describe table” command1, copying and pasting that output into your report. Marks are available for: creating the tables, having the right links between the tables, creating appropriate sample data, and inserting the data/populating the database. If your model (sections one to four) is more complex than your database, that is not the end of the world; describe where your implementation falls short, and describe the decisions you have taken when considering which bits to leave out. [30 marks] 6. Write some SQL queries to perform the following tasks: i. Find all tutors who teach maths classes ii. You have a new student who wants tuition in Art in Aberystwyth. Find all appropriate tutors. iii. Find all students who don’t match tutors (that is, students for whom there is no tutor matching on both location and subject). [10 marks] 7. Self evaluation: what mark do you expect for each section, and why? [5 marks] Submission The submission should be a single pdf document containing the following sections: 1. Modelling the problem 2. Candidate keys and functional dependencies within the data 3. From UNF to 3NF: an account of the normalisation process 4. ER diagram 5. Implementation in PostgreSQL 6. Some sample queries 7. Self evaluation For those aspects which require specific SQL to be written and executed, you must paste the code (and its output) into your report. In case of any personal, financial or health problems affecting this coursework, please contact your year tutor, Annie Shaw
[email protected] 1 See guidance below if you are not sure about this Guidance UNF This should list the attributes you’d expect to find, with some note of the types that the data would have. You don’t necessarily have to present it in a “large table”; a list or set of lists will do. An adequate solution would simply list the attributes and types. A better solution would include a discussion of how these had been decided, and a description of what they would contain. Candidate keys and Functional Dependencies An adequate solution to this part of the assignment will list the attributes in the un- normalised structure, indicate the primary key along with any candidate keys, and will also show repeating groups of attributes and important functional dependencies. A better solution will also include an English sentence describing each functional dependency and will also justify the choice of primary key. Bottom-up-analysis, UNF -> 3NF An adequate solution will carry out each step based on the attributes and functional dependencies identified in the first part of the assignment. A better solution will also provide a coherent and accurate explanation of each step in terms of the functional dependencies. Entity Relationship Modelling An adequate solution will have a sketch of an ER diagram, with cardinality constraints indicated. A better solution will have all cardinality constraints included, use consistent formatting, and be accompanied by a good description of each relationship. Implementation in PostgreSQL To describe a table in postgreSQL, type \d table_name at the command prompt. The output of this should then be pasted into your report (suitably formatted with a fixed- width font). For example, this might be (part of) the output: Table "hmd1.student" Column | Type | Modifiers ------------------+-------------------------+----------- studname | character varying(500) | studcontact | character varying(500) | studtopic | character varying(500) | studaddress | character varying(500) | If you have not implemented your entire model, you should explain why. An adequate solution would list the tables. A better solution would talk about the implementation and explain what was going on. Queries An adequate solution will list each query and its outputs. A better solution will give reasons and a description of the implementation along with a good description of what each SQL query is achieving. Self evaluation An adequate solution should include your expected mark for each section. A better solution will include an explanation saying why you think you should get that mark and commentary on any shortcomings.
