1. Tasks: (Read the attached file for complete requirements (Must be finished tomorrow any time)
The overall objective of this assignment is to build a database application for a real-world scenario Video CD and video game rentals SQL Database–. 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). 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. When expressed in the entityrelationship 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 class and the textbook 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.