User Account

All Pages

Database management, Computer Networking

Assignment Help:
University of Wolverhampton
School of Technology
6CI007 Database Server Management
Resit Assessment
Hand in December 14th 2012
In this assignment you will build a small database to support the needs of a private library company called Libloan. To do this you will need to create tables to support the following scenario.

Libloan is a small library company that lends books to customers. Customers may borrow books for a period of 5 days and pay fees to hire the books of between one and five pounds depending on the book. The start date of each hiring of a book, and the borrower are recorded. Customer names, gender, telephone numbers, and address need to be recorded. Each time a customer borrows a copy they will give an opinion of the book (the evaluation is a number from 0 to 5). Libloan need to record the evaluation of each book by customers who borrow it. It is not a requirement that all books recorded on the system have copies in the library and it is also not required that they are actually borrowed. Neither is it required that every customer has to have borrowed a book. In order to cater for demand, the library stocks multiple copies of books in different locations (towns) as appropriate.

1. Design an Entity model and construct a set of tables with suitably defined columns to support this scenario.
15 marks

2. Populate the tables with appropriate test data, bearing in mind the following :

a. There may be some books that have yet to be borrowed.
b. There may be some customers who have yet to borrow a
book.
c. All copies of books are uniquely identified by a single key column called copy_id
d. Loan records are identified by a combination of foreign key columns (not by a single column surrogate key)

Hint: In order to keep the amount of test data to a minimum, we will assume that Libloan is unlikely to run with more than six book titles and that the number of customers about which they have details is less than 11.

Take a look at the query specifications in question 4. You will need to have data values that are relevant to these queries. BUT the design of your tables and test data should not be compromised merely to support just these queries.

The creation of a good set of WELL DESIGNED test data is VERY IMPORTANT.
15 marks

6CI007 Database Server Management
Resit Assessment (continued)

3. Create appropriate primary and foreign key constraints on the tables.

15 marks

4. Develop a set of queries as follows :

a. Find details of all books stocked in London

b. Find the book that has the most copies.

c. Find the total number of hirings of each book.

d. Show details of the books with the total revenue that they each generate based on the hirings of copies of those books.

e. Show details of customer names and the titles of the books that they have borrowed

f. Write and test a query to list the title and purchase price of each book. Add a column that compares the cost of the book to the average cost i.e., shows the difference between the book cost and the average cost of all books.

g. List all customers who have not hired a book located in York.

h. Find the copies of books that have their number of hirings below
the average hirings for copies of that book.

i. Write and test a query to list the customer ID and name of every
Customer along with the books that they have hired within the past 200 days. Include starting date, ending date, and location name for those hirings. All customer details (ID and name) should be included in the output, whether or not they have actually borrowed any books.

j. Find names of all female customers who have borrowed a book
in 2005.

30 Marks


6CI007 Database Server Management
Resit Assessment (continued)

5. Develop a view that shows details of all books on record, along with the details of their copies. Include books for which there are presently no copies in the library. Display the contents of the view.

Test the view to show its ability to support DML activity. This should include examples of :

i Separate updates on each individual column within the view
ii Inserts of new rows into the view
iii Deletes of rows from the view

Comment on the outcomes with respect to the ‘updatability’ of the view

Hint : You may wish to rollback any changes you have made to the data in order to obtain a consistent set of test data ready for question 6.

10 Marks

6. Create triggers that enforce the following business rules :

a Ensure that copies of books stored in London cannot be
borrowed during December.

b If a customer gives a zero evaluation, the details of their hiring (customer name, which book, the date of hiring, location of the copy and evaluation) must be placed in an audit table.

Hint: You will need to construct an audit table with the appropriate columns of correct datatype.

c Ensure that records cannot be deleted from the loan table if the start_date of the loan is earlier than the current date.


Test all of your triggers with statements that fire the trigger.
Show the trigger code and the output that they generate
Discuss their behaviour in your tests.
15 Marks

6CI007 Database Server Management
Resit Assessment (continued)

The Hand-in

Your report should include

i A simple entity model (Extended Entity Relationship diagrams are not necessary)

ii All table creation statements

iii The SQL used to create your constraints

iv Listings of all test data and an explanation of the chosen test data. (Use SELECT * FROM <>; to show the test data. Do not show the INSERT statements)

v The view definition and how its data can be changed (and why)

vi The trigger definitions and demonstrations of how they work.

vii The output of all queries (properly formatted and presented) along with the code used to generate the output.

Hint : IMPORTANT : Use courier new font for presenting all code and test data (see style adopted in lecture notes)

IMPORTANT : Avoid wraparound effects when displaying output in SQL*Plus (use column formatting commands and LINESIZE commands)

PLEASE NOTE:
This is an individual assignment.
Submit the assessment to the Student Office in the MI building.
Penalties for late submission of coursework
Standard School of Technology arrangements apply.
"ANY late submission (without valid cause) will result in the grade F0 being allocated to the coursework".

Grade Attainment Criteria
The following is given as a general guideline only. Marks may vary away from this rigid framework based on the professional judgement of the module team and the overall performance of each student in attempting to accurately reflect the scenario.


6CI007 Database Server Management
Resit Assessment (continued)
Grading Criteria
Grade A:
An entity model that correctly reflects the scenario
A set of tables that adheres to the entity model and scenario
Properly designed test data and constraints
A correct set of SQL queries
A thoroughly tested view with discussion on its updatability
Triggers that are properly coded and tested with illustrative examples

B: The answer must almost correctly reflect the scenario
A set of tables that generally adhere to the entity model and scenario
Properly designed test data and constraints
A set of SQL queries that is mostly correct
A properly constructed view with partial testing and discussion
Triggers that are properly coded and tested

C: The answer must generally reflect the scenario
A set of tables that generally adhere to the entity model and scenario
Properly designed test data and constraints
A set of SQL queries that is generally correct
A properly constructed view with some testing and discussion
Triggers that show evidence of testing

D: The answer must be at least supportive of the scenario
A set of tables that reflect the entity model and scenario in some meaningful way
Test data and constraints that support most of the queries
A set of SQL queries that show evidence of SQL knowledge and skills
A view with some testing and discussion
Triggers that show some evidence of trigger processing knowledge

E: The answer does not reasonably reflect the scenario although some good points are included.
Entity-relationship diagram produced, but has major flaws.
Few queries correctly specified
View has little or no discussion of its updatability
Little evidence of triggers and testing
F: The answer does not reasonably reflect the scenario and has no
redeeming features.

This assignment is intended as an individual piece of coursework. On no account should you work on the assignment in groups to produce a group answer.

Related Discussions:- Database management

Illustrate 802.11b HR DSSS, 802.11b HR DSSS a) High-rate DSSS using a ...

802.11b HR DSSS a) High-rate DSSS using a 2.4 GHz band b) Similar to DSSS excluding for encoding method c) Uses complementary code keying (CCK), encoding 4 or 8 bits to

Troubleshoot and correct vtp and configuration errors-ccna, Troubleshoot an...

Troubleshoot and Correct VTP and Configuration Errors When all errors are corrected, you should be capable to ping PC4 from PC1, PC5 from PC2, and PC6 from PC3. You should also

Routing table - network layer and routing , Routing Table A routing  t...

Routing Table A routing  table has columns  for at  three types o information  the network  ID  the cost  and the  ID of the  next  router. The  network  ID is the final  desti

Sonet frame format - sonet sdh, SONET Frame Format SONET  uses  a basic...

SONET Frame Format SONET  uses  a basic  transmission  rate of STS -1 that  is equivalent to 51.84 mbps. Higher level  signals  are integer multiples of the base rate. For  exa

Hybrid networks -v irtual private networks , Hybrid Networks Allows  a...

Hybrid Networks Allows  an organization to have its  own  private networks  and at the same  time access to the global internet. the organization  may share  its information w

What is the standard isdn term for a native isdn modem, TA is the standard...

TA is the standard ISDN term for a native ISDN modem

Classify different types of ip routing, INTERNETWORKING - TCP/IP 1. Sta...

INTERNETWORKING - TCP/IP 1. State the following terminologies: a) Node b) Router c) Host d) Subnet e) Network 2. What do you understand by IEEE 802 Local Area network 3.

Reliability probability of success, Reliability is the probability of succe...

Reliability is the probability of success ; where success is defined as compliance to specified requirements. A system or an item is considered reliable if it performs satisfactori

Tcp connection, Lat take the CONNECT (client side), LISTEN, and ACCEPT (ser...

Lat take the CONNECT (client side), LISTEN, and ACCEPT (server side) socket primitives a) Why are these three needed for a TCP connection? b) Why are none of them needed for a U

Enable wireless security - ccna, Enable Wireless Security Step : Confi...

Enable Wireless Security Step : Configure Windows to use WEP authentication.  Navigate to the Network Connections page again and right-click the Wireless Network Connection

Write Your Message!

Captcha
Order Assignment

Featured Services

Order Now

Popular Subjects

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +91-977-207-8620

Phone: +91-977-207-8620

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd