1. 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...

1. 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.