ACME Arts College offers on-campus night courses in a range of educational areas across three semesters per year. Students can enrol in multiple courses in a semester but they can only enrol in a course if they meet the prerequisites and haven't passed the course before. In addition to a students final grade for each course, ACME also stores the students id number, name, and contact details (phone, address). Students can either enrol directly as a full fee paying student or on an arts scholarship. For scholarship students we record the granter of the scholarship the year and amount of the grant.
Most of ACME's instructors are working professionals, so in addition to instructor id, name and contact details, ACME also tracks each instructors verified skills and certifications.
Each course has a course code, a title and a list of prerequisite courses. For example you can't enrol in ART3001 until youve passed ART1001 and ART2003. Each of the courses can have multiple offerings in a year, and each offering will have an instructor, a scheduled class time and classroom and an enrolment capacity. Each course may also have a list of required supply items which can be purchased from ACMEs Campus Store.
The campus store maintains a list of all required supply items for each course. Multiple courses may use the same supply items. For each item the store records its name, price, category (painting, sculpting, photography, or drawing) and the supplier it can be purchased from. Some items can be purchased from multiple suppliers. Some suppliers provide multiple items. Each supplier's company name and contact details are recorded.
Prepare the following:
a)An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.
b)A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.
c)An Oracle SQL table create statement for the relation that you think is most critical in this system. This relation must have a primary key and at least one foreign key.
a)Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. Show your working and entities for 1NF, 2NF and 3NF. You must use the Finkelstein methodology as used in the study book and tutorials.
CAR PURCHASE CONTRACT (contract number, contract date, details of purchase, total purchase amount, car delivery date, customer number, customer name, customer address, customer email, car stock number, car registration number, make, model, colour, body type, VIN number, engine number, manufacture date, manufacturer code, manufacturer name, manufacturer contact details)
Notes:
1.One car purchase contract is a legally binding agreement between one customer and a car offered for sale.
2.A car is identified by the car stock number and not the car registration number.
3.A car is manufactured by a car manufacturer.
4.There are no repeating groups associated with a car purchase contract.
Below is a reproduction of the ERD for the JustLee books database. It should help you navigate the tables in the database. The database script to create the tables is located on the Moodle site under the assignment specifications. If you have run a version of the script earlier in the semester please run it again to ensure that you are using the correct version of the tables. The ERD diagram may not necessarily have all the fields listed so you might have to refer to the description of the table by using the DESCRIBE command in Oracle
Figure 1-5 JustLee Book's table structures after normalization: see image.
The questions in this section are challenging. Most require a number of tables and/or nested queries. When solving each question it is best not to try and write the solution as a single activity. Instead try and write a separate query to solve each of the parts and once you understand the data and the results rewrite the query into a solution.
For each question,
1)Display the category, book title, retail cost (formatted as $XXX.XX) and gift details for all books which are eligible for a gift. Order the result by category and retail cost (retail cost in descending order).
2)Display the last name, first name, order number for all customers who have placed an order for which they have not received a free gift of "BOOK LABELS" listed in the promotion table. Here we want to use the amount that they paid for each item to see is they were eligible for a gift of BOOK LABELS. The gifts are only available for the price paid and does not take into consideration the quantity of a book purchase.
3)Display the unique customer number, customer last name, customer first name for all customers who have bought a book that was co-authored by more than 2 authors. Order the resulting set by customer number.
4)Display the book title and the publisher name of all the books that have been written by a single author and where that book has had no sales recorded.
5)Display the publisher name, contact details and phone number for all the publisher that have had sales of more than 5 book from the list of books they publish.
6)Display the state, count of number of orders (rename the field "Number of Orders"), and the number of book categories (rename the field Number of Categories) for all states that have the number of book categories same as the highest number of book categories in all the states. We are looking for states that buy the most diverse categories of books.