Database Systems 2017 - 2018Practical Assignment1. Tasks:The idea behind this practical assignment is give you an opportunity to demonstrate and showcase what you have learnt about data modelling, relational database design and SQL. To do so, you are asked to build a database application for a real-world scenario – which is allocated to you by your tutor. You will design a conceptual as well as a relational schema for the database, and you will create an actual database using a relational database management system (Oracle or another DBMS of your choice – but NOT Access!). You will populate the database with sample data and write interactive queries and modifications on the database. More specifically, the tasks you will need to perform for this assignment are as follows:o Your first step is to describe the requirements for your assigned scenario. Your requirements should include data requirements and a sample of transaction requirements. You can see an example on how to express data application requirements in the appendix (see end of this document).o Construct an entity-relationship diagram for the data (see Chapter 4). When expressed in the entity-relationship model, you might want your design to have in the range of five or so entity sets/classes, and a similar number of relationships. Be aware, however, that certain entity sets/classes or relationships could sometimes be represented by attributes. You should certainly try to include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.), but your application is not required to use advanced features, such as sub-classing, multi-way relationships, or weak entity sets, if they are not appropriate for your application.o Use the method for translating an E/R diagram to relations described in the textbook (Chapter 5) to produce a set of relations from your E/R design. Make sure you specify explicitly the various keys (primary and foreign) in your relational schema. Are there any flaws in the relational database schema you get from this translation process? Are there opportunities to combine relations without introducing redundancy? If so, indicate which, and if not, tell us there are none. Are there examples of non-BCNF relation schemas? If so, do you want to decompose them? Is there anything you still don't like about the schema (e.g., attribute names, relation structure, duplicated information, etc.)? If so, modify the relational schema to something you prefer.o Write an SQL database schema for your scenario, using the CREATE TABLE commands. Pick suitable data types for each attribute. Page 119 of the textbook gives you the principal options regarding types (remember Oracle likes you to use VARCHAR2 for character strings, so please use this type).o Choose and declare primary keys and (if appropriate) other unique attributes or sets of attributes. Similarly, declare the foreign keys within your schema.o Execute INSERT commands to insert tuples/records into one of your relations (this data is chosen randomly by you). Show the response of sqlplus and the relation that results when you issue a SELECT * command.o Your database almost certainly includes relations that are expected to join with each other. For example, you may have a Student relation with attribute courseNo that's expected to join with attribute number in relation Course. In generating data, be sure to generate values that actually do join - otherwise all of your interesting queries will have empty results! One way to guarantee ‘joinability’ is to generate the values in one relation, then use the generated values in one relation to select joining values for the other relation. For example, you could generate course numbers first (either sequentially or randomly), then use these numbers to fill in the courseNo values in the student relation.o Write five queries on your database, using the SELECT construct of SQL. To receive full credit, each of your queries should exhibit some interesting feature of SQL: subtotals, sub-queries, or queries over more than one relation, for example.o Write three data modification commands on your database. These commands should include updating one or more record(s) and deleting one or/more record(s).o Create two views on top of your database schema. Show your CREATE VIEW statements and the response of the system. Also, show a query involving each view and the system response. Finally, show a script of what happens when you try to modify your view, say by inserting a new tuple into it. Is either of your views updatable? Tell why or why not? (essentially, a view is updatable if it is a selection on one base table.)o Add two attribute-based CHECK constraints to relations of your database schema. Show the revised schema, its successful declaration, and the response of the DBMS to inserts or updates that violate the constraints.o Conclude your work with a piece of reflection. Reflecting on the whole assignment does not mean that you just describe what happened whilst doing the assignment. You will also need to cover what you have learnt: what went well, what went wrong, how you overcame the challenges, what you would you do differently in the future, … etc. This is an important section of the report, so make sure you spend some time thinking about it and writing it.2. Report:Your well-structured report should contain and describe all of the above tasks. All the SQL scripts should be included as well as a narrative around all the tasks. You will need to explain and describe what you are doing as you go along performing each task.You will need to submit an electronic (soft) copy of your report to turnitin (via Moodle) by 10pm on 13/5/2018. If you submit any later than that this deadline (even by one minute!) then the normal university regulations regarding late submissions will apply (in other words, your mark will be capped).3. Demonstration/Sales Pitch:On 14/5/2018, you will be expected to talk through your assignment with your tutor. This will be in the form of a ‘sales pitch’. You will have between 5 and 7mins to ‘sell’ your work to your tutor. The sales pitch will consist of two main things: (a) explain briefly your database modelling and design, and then proceed to (b) demonstrate the implementation. The latter should be done in the form of running some of the above queries live and showing the expected results. Make sure (a) you have these ready (and perhaps just use copy and paste on the day), and (b) you have tried/ran them before (and they worked!).
4. Academic Misconduct and Plagiarism:Please note that this is an assignment to be worked on *individually*. Therefore, all the work you submit should be produced by you and you alone. Anything else would be liable to be considered as academic misconduct (cheating and plagiarism). The University regulations regarding academic misconduct can be found in this document:https://www.lsbu.ac.uk/__data/assets/pdf_file/0010/84349/assessment-and-examinationhandbook.pdfThe section relating to academic misconduct can be found on page 28. You have agreed to abide by these regulations so make sure you understand them.If still unclear and in doubt, do discuss the matter with your tutor.5. Easter Break:Even though the submission is in week 13, I strongly recommend that you try to do as much work on the project/assignment as possible during the break. If you have any queries about the assignment during Easter then it might be a good idea to post them on Piazza (please don’t email your tutor). That way: (a) there is a good chance that one of us 4 tutors or other students will answer it in good time, and (b) other students who might have a similar query will benefit from seeing it answered.Marking Scheme Criteria GuidelinesThe following will be used as guidance in marking the assignment. Each task results in a mark on a 0-10 scale, which is multiplied by a weighting in order to obtain a score for that task. This table explains the awarding of each mark of 0 -10. MARKSTASK0-4 MARKS (REALLY QUITE INADEQUATE, ‘FAIL’)4-5 MARKS (ADEQUATE BUT NO MORE)5-6 MARKS (GOOD AVERAGE WORK)6-7 MARKS (WELL ABOVE THE AVERAGE)7+ MARKS (QUITE EXCEPTIONAL)Application Requireme nts (weighting: 0.1)Very poor in all aspects. Requirements specification inadequateLimited description of the requirements. Weak narrative.Reasonable description of the requirements. Narrative covers the basic requirements.Very good and fairly complete description of the requirements.Excellent in all aspects. Full description of the requirements.ER Diagram (weighting: 0.2)Significant notational and significant interpretation errors. No narrative or very poor narrativeNotational errors, poor interpretation of ER modelling technique Weak narrative. limited discussion of key issuesNotation correct, reasonable interpretation of ER modelling technique. Narrative covers the basic issues adequately.Notation correct, reasonable interpretation. Good supporting narrativeExcellent in all respects. Supporting narrative accurate, reflective and critical.Relational Database Design (weighting: 0.1)Significant notational and significant interpretation errors. No narrative or very poor narrativeNotational errors, poor interpretation of translation technique. Weak narrative. limited discussion of key issuesNotation correct, reasonable interpretation of translation technique. Narrative covers the basic issues adequatelyNotation correct, reasonable interpretation. Good supporting narrativeExcellent in all respects. Supporting narrative accurate, reflective and critical.Database Creation and Population (CREATE, INSERT) (weighting: 0.1)Weak, poorly coded, basic structural omissions, probably little relation to design models. No narrative or very poor narrativeAcceptable code Basic Relational implementation with some omissions errors, inconsistencies. Weak narrative. limited discussion of key issuesEssentially correct use of code notation, probably some omissions. Narrative covers the basic issues adequatelyGood use of coding techniques fairly accurate attempt at implementation of business rules. Good supporting narrativeExcellent in all respects. Program code clearly capable of successful task completion. Supporting narrative accurate.Main SQL Tasks (SELECT, UPDATE, DELETE (weighting: 0.2)Weak SQL with substantial errors in query structure. No narrative or very poor narrativeEssentially correct SQL notation but probably weak interpretation. Weak narrative. Limited discussion of key issuesSQL that is mostly correct. Some errors in code. Narrative covers the basic issues adequatelyMost SQL fundamentally correct. Appropriate attempts to implement ‘interesting’ queries. Good supporting narrativeAll SQL aspects correct or only very minor errors/omissions. Excellent attempts to implement ‘interesting’ queries. Supporting narrative accurate.Additional SQL Tasks (Views, CHECK) (weighting: 0.1)Weak SQL with substantial errors in query structure. No narrative or very poor narrativeEssentially correct SQL notation but probably weak interpretation. Weak narrative. Limited discussion of key issuesSQL that is mostly correct. Some errors in code. Narrative covers the basic issues adequatelyMost SQL fundamentally correct. Appropriate attempts to implement ‘interesting’ queries. Good supporting narrativeAll SQL aspects correct or only very minor errors/omissions. Excellent attempts to implement ‘interesting’ queries. Supporting narrative accurateReflective Statement (weighting: 0.1)Inadequate statement. Not much learning seems to have taken place. Shows no ability to reflect.Adequate statement. Limited amount of learning seems to have taken place. Shows some ability to reflect.Good statement. A fair amount of learning seems to have taken place. Shows a good ability to reflect.Very good statement. A substantial amount of learning seems to have taken place. Shows a very ability to reflect.Excellent statement. A huge amount of learning seems to have taken place. Shows an excellent ability to reflect.Demo / Sales Pitch (weighting: 0.1)Technically inept. No evidence of planning.Adequate performance. Some evidence of planning.Good performance. Reasonable evidence of planning.Very good performance. Clear evidence of planning.Excellent performance. Meticulous planning.
Appendix User's Requirements Specification for DreamHome (Estate Agency) Case Study1. Data RequirementsStaff Members of staff with the role of Supervisor are responsible for the day-to-day activities of an allocated group of staff called Assistants (up to a maximum of 10, at any one time). Not all members of staff are assigned to a Supervisor. The data stored on each member of staff includes staff number, name, address, position, salary, name of Supervisor (where applicable), and the details of the branch office at which a member of staff is currently working. The staff number is unique across all branches of DreamHome.Properties for rent Each branch office offers a range of properties for rent. The data stored on each property includes property number, address (street, city, postcode), type, number of rooms, monthly rent, and the details of the property owner. The property number is unique across all branch offices. The management of a property is assigned to a member of staff whenever it is rented out or requires to be rented out. A member of staff may manage a maximum of 100 properties for rent at any one time.Property owners The details of property owners are also stored. There are two main types of property owner: private owners and business owners. The data stored on private owners includes owner number, name, address, and telephone number. The data stored on business owners includes name of business, type of business, address, telephone number, and contact name.Clients DreamHome refers to members of the public interested in renting property as clients. To become a client, a person must first register at a branch office of DreamHome. The data stored on clients includes client number, name, telephone number, preferred type of accommodation, and the maximum rent the client is prepared to pay. Also stored is the name of the member of staff who processed the registration, the date the client joined, and some details on the branch office at which the client registered. The client number is unique across all DreamHome branches.Leases When a property is rented out, a lease is drawn up between the client and the property. The data detailed on the lease includes lease number, client number, name and address, property number and address, monthly rent, method of payment, an indication of whether the deposit has been paid (deposit is calculated as twice the monthly rent), duration of lease, and the date the lease period is to start and finish.Newspapers When required, the details of properties for rent are advertised in local and national newspapers. The data stored includes the property number, address, type, number of rooms, rent, the date advertised, the name of the newspaper, and the cost. The data stored on each newspaper includes the newspaper name, address, telephone number, and contact name.Property viewings Clients may request to view property. The data stored includes client number, name and telephone number, property number and address, date the client viewed the property, and any comments made by the client regarding the suitability of the property. A client may view the same property only once on a given date.Leases Once a client finds a suitable property, a lease is drawn up. The information on the lease includes lease number, client number and name, property number, address, type and number of rooms, monthly rent, method of payment, deposit (calculated as twice the monthly rent), whether the deposit is paid, the date the rent period starts and finishes, and the duration of the lease. The lease number is unique across all DreamHome branches. A client may hold a lease associated with a given property for a minimum of three months to a maximum of 1 year.2 Transaction Requirements (Sample)Data entry Enter the details of a new branch (such as branch B003 in Glasgow). Enter the details of a new member of staff at a branch (such as Ann Beech at branch B003). Enter the details of a lease between a client and property (such as client Mike Ritchie renting out property number PG4 from the 10-May-03 to 9-May-04). Enter the details of a property advertised in a newspaper (such as property number PG4 advertised in the Glasgow daily newspaper on the 06-May-03).Data update/deletion Update/delete the details of a branch. Update/delete the details of a member of staff at a branch. Update/delete the details of a given lease at a given branch. Update/delete the details of a newspaper advert at a given branch.Data queries Examples of queries required by the Branch user views: