Hi, I have a Database course work which is due in couple of weeks is there any chance you can help me? and its a file so how would I send it to you? Many thanks
SCHOOL OF ARCHITECTURE, COMPUTING, & ENGINEERING
Cover sheet to be attached to the front of the assignment when submitted
All pages to be numbered sequentially
All work has to be presented in a ready to submit state upon arrival at the ACE Helpdesk. Assignment cover sheets or stationery will NOT be provided by Helpdesk staff
Design and Implement a Database System
One (INDIVIDUAL WORK)
13 August 2014
Learning outcomes assessed by this assignment
1, 5 to 8
Turnitin submission requirement
Turnitin GradeMark feedback used?
UEL Plus Grade Book submission used?
UEL Plus Grade Book feedback used?
Other electronic system used?
Are submissions / feedback totally electronic?
Page 2 of 5
Form of assessment:
Individual work Group work
For group work assessment which requires members to submit both individual and group work aspects for the assignment, the work should be submitted as:
Consolidated single document Separately by each member
Number of assignment copies required:
1 2 Other
Assignment to be presented in the following format:
On-line submission Stapled once in the top left-hand corner Glue bound Spiral bound Placed in a A4 ring bound folder (not lever arch)
Note: To students submitting work on A3/A2 boards, work has to be contained in suitable protective case to ensure any damage to work is avoided.
CD (to be attached to the work in an envelope or purpose made wallet adhered to the rear) USB (to be attached to the work in an envelope or purpose made wallet adhered to the rear) Soft copy not required
Note to all students
Assignment cover sheets can be downloaded from UEL Plus via the following pathway.
Home Page → ACE Information → ACE Helpdesk → Assignment Front Sheets
All work has to be presented in a ready to submit state upon arrival at the ACE Helpdesk. Assignment cover sheets or stationery (including staplers) will NOT be provided by Helpdesk staff. This will mean students will not be able to staple cover sheets at the Helpdesk.
Page 3 of 5
STUDENT INDIVIDUAL TASK:
You are required to design and implement a database system in Oracle based on the case study below. You will be expected to identify the management needs of the organisation and present how they are solved and dealt with by your database design and implementation.
You are required to submit:
1. Weighting 40%
a) A fully annotated E-R diagram 1 and 2 showing the entities, primary and foreign keys, composite keys, relationships and optionality.
b) A relational schema of your database in 3NF, clearly indicating attributes, the data type of each attribute, primary and foreign keys and which attributes are nullable, giving reasons. List any assumptions you need to make.
c) Listings of the records in each table. There should be at least 10 records in each.
2. Weighting 20%
a) Ensure consistency of data in the database by developing integrity and referential integrity constraints that will ensure the correctness of the data when it is entered.
3. Weighting 35%
a) Produce SQL listings on the specific questions based on the Case Study.
4. Weighting 5%
a) Documentation must be logically organised with clarity and completeness. A hard copy as well as an electronic copy saved as a rich text file on CD, or any other storage medium, which is readable, by the network drives.
Page 4 of 5
Case Study: Abbey College Student Accommodation
The Accommodation Office in Abbey College, located in Surry, wishes to create a database to monitor the allocation of accommodation to students. Each student requiring accommodation completes an application form, which holds the students’ details and an indication for the type of accommodation required and the duration. Students may rent a room in a hall of residence or student apartment. The halls provide only single rooms, which have a room number, place number and monthly rental rate. The place number uniquely identifies each room in all the halls controlled by the Accommodation Office and is used when renting a room to a student. Each hall is managed by a member of the accommodation office.
The Accommodation Office also offers student apartments, each identified by a unique apartment number. These apartments are fully furnished and provide a single room accommodation for groups of three, four or five students. Each bedroom in an apartment has a monthly rental rate and a room number. The place number uniquely identifies each room available in all student apartments and is used when renting a room to a student. Apartments are inspected by members of the Accommodation Office on a regular basis to ensure that the accommodation is well maintained.
New lease agreements are negotiated at the start of each academic year with a minimum rental period of one semester and a maximum of one year. The students pay for their accommodation throughout the academic year and are sent an invoice at the start of each semester. If a student does not pay by a certain date, two reminder letters are sent.
There have been many problems in the past in relation to bookings of rooms, students not paying their rent on time or not at all, or students paying their rent and no record of it being found. Due to these and many other problems it has been decided that a fully computerised system be installed that will store all the room and student details.
In order meet the business needs and ensure consistency of data in the database you are to develop integrity and referential integrity constraints that will ensure the correctness of the data when it is entered. Below is a list of checks you should implement:
1. Student gender should be recorded as ‘M’ or ‘F’.
2. The employee’s salary should be greater than £0 and not exceed £75,000.
3. Each student’s end-date of the lease should not be earlier than the start-date.
4. Create a table without placing the Primary and Foreign Key constraint. Then alter the table to include the constraint for a foreign key and a primary key.
Page 5 of 5
5. The employees’ job can only be one of the following ‘Administrator’, ‘Manager’, ‘Supervisor’, ‘Clerk’, ‘Care Taker’, ‘Secretary’, ‘Accountant’.
6. Demonstrate the SQL to drop the job check constraint previously created in 5 above.
7. Ensure that the student telephone number cannot be left blank.
8. The college hall’s names must be unique.
9. Student details should have an email address with the @ symbol present to be valid.
10. Student numbers should be generated automatically using a sequence and a trigger.
The database should also be able to answer the following queries:
1. List all students’ details. [2 marks]
2. Display all employees who were recruited during 2010, giving their name and hire-date. [3 marks]
3. List employee’s name, job and salary who conduct the inspection of a specific apartment. [4 marks]
4. Give a count of the number of student in a specific hall that started 1 October 2009. [4 marks]
5. List all the different rooms and the hall they are located in. Order by hall name. [5 marks]
6. List the student name, room number and lease details. [5 marks]
7. List employee names and salary that manage the halls and increased their salary by 10% and expressed as a final whole figure. [6 marks]
8. List the Room number, the apartment it is in, and the students who are living in those rooms. [6 marks]