At the bottom of this document are the requirements for a database application. You are required to design and implement an Oracle application that satisfies these requirements. This includes setting up a database schema and providing access methods to this in the form of queries and views. The steps for doing this include:
Creating a conceptual schema in the form of an ER or UML diagram.
Deriving a relational schema from the ER diagram.
Normalising the relations.
Implementing this schema by using SQL in Oracle.
Populating the database with a set of typical data. The data should be significant but manageable.
Defining specialised views which are appropriate to various sub-groups of users.
Defining SQL queries which could be used as canned queries for naive users.
There are two separate courseworks. Coursework 1 consists of the design of the database, and coursework 2 is the database implementation using SQL.
This coursework is to be done in teams of no more than 4 people. An ideal number is 3. You should form the teams yourselves.
Coursework 1: DESIGN
Date due: Friday the 30th of October 2020.
This deliverable is concerned with the design of the database. Your report should contain the following components:
- The conceptual schema (ER or UML diagram) together with an explanation of how you derived the model (5 marks)
- A list of all assumptions you have made in your design (no marks but you will probably find it useful to state these).
- The relational database schema for your chosen application. Here you should show how you translate the ER diagram into the relational schema. You should also identify all primary and foreign keys in your design (5 marks)
- The normalised design for your application. You should produce a normalised design of your application, and explain why it is in 3rd Normal Form (5 marks)
(total 15 marks)
Database Application requirements - College Library System.
A college library provides various resources for students and staff, including books, videos, DVDs and CDs. It is common that several copies are kept of some resources, for example recommended books for courses. The usual loan period of a resource is 2 weeks, but some resources are available for short loan only (2 days) and some other resources can only be used within the library.
The library consists of 3 floors. Resources are stored in the library on shelves. To locate a specific item in the library a combination of floor number and shelf number are used. In addition to this, a class number system is used to identify in which subject area a particular item belongs, for example all resources concerned with Database Systems will have the same class number.
Students hold library cards which identify them as valid members of the Library. Students can lone a number of different resources at one time, but the total number of resources they may borrow at a given time must never exceed 5. Staff members at the College also hold library cards, and are allowed to lone up to 10 different items at one time.
The library charges fines for resources that are loaned for longer than the time allowed for that resource. For each day a resource is overdue the member is fined one dollar. When the amount owed in fines by a member is more than 10 dollars, that member is suspended until all resources have been returned and all fines paid in full.
The system is required to maintain a record of all of the above details. Specifically it needs to keep track of:
- What each resource is, its class number, how many copies of it are held by the library and where these are located in the library.
- Student and staff members of the library.
- All current loans, including whether they are overdue.
- A record of previous loans to help in identifying popular resources.
- The details of any fines owed by members.
- a list of library members who have been suspended due to overdue loans or unpaid fines.
Marking schemes for coursework 1
The following marking scheme is provided to assist you in judging the standard of work required for your coursework submission.
To obtain a mark greater or equal 70% (Distinction): all parts of the required work must be completed to an excellent standard, including a high level of detail and accuracy. Evidence of an ability to address issues beyond the given specification, and use of materials beyond those in the taught course will normally be expected for this grade.
To obtain a mark between 60-69% (Merit): Most parts of the work will be completed to a high standard of detail and accuracy, though the work may include a small number of minor errors. The work should demonstrate a good and consistent level of ability across the full range of requested deliverables. The work may at times demonstrate the characteristics of work deserving of a mark over 70%, but at the same time include flaws which prevent it from being rated at that level.
To obtain a mark between 50-59% (Pass): Most deliverables should be developed in a competent manner, though a higher number of minor errors, or a small number of more significant errors may be present.
Contact us to get instant help at firstname.lastname@example.org and get instant help with an affordable prices