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

Security - fundamentals of networks, Security Security  is the  protec...

Security Security  is the  protection of hardware software and data  from  unauthorized access. Restricted physical  access to computer password protection limiting  user priv

Explain about bandwidth – delay product, Q. Explain about Bandwidth – delay...

Q. Explain about Bandwidth – delay product? Bandwidth - delay product - A measure of competence of ARQ system = bandwidth (bits per second) * round-trip delay (in seco

Explain typical network topologies, Question: a) Explain briefly three ...

Question: a) Explain briefly three typical network topologies giving one advantage and one disadvantage of each topology. Explain the three topologies with appropriate diagrams

Describe any good security technology, Describe any good security technolog...

Describe any good security technology A good security technology should be powerful enough to support the features that the administrators need, including rules validation to i

Maintaining the sequence number, Maintaining the sequence number The  ...

Maintaining the sequence number The  other advantage of GBN  is that  it maintain  the next  sequence number.

What are gateways, What are gateways? Gateways give connectivity among ...

What are gateways? Gateways give connectivity among two or more network segments. It is usually a computer that runs the gateway software and gives translation services. This t

High availability - application layer, High Availability Oracle web  c...

High Availability Oracle web  cache supports  content  aware  load  balancing  and fail  over  detection. The  features ensure that cache messes are directed to the most  avai

Create and simulate a network - netml system, Create and simulate a network...

Create and simulate a network similar to the one shown here, using the NetML system. The network is a little more complex, as depicted at the right. There is an extra node

Karnaugh map, Qustions: Simplify the following expression using a Karna...

Qustions: Simplify the following expression using a Karnaugh map: F = XY‾Z + X‾Y‾Z + XY Z +X‾ Y Z

What is the frame relay, What is the Frame relay, in which layer it comes? ...

What is the Frame relay, in which layer it comes? Ans) Frame relay is an industry standard, shared access, switched Data Link Layer encapsulation that services many virtual circ

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