Video Hire Database
A video hire business needs a database to manage the loans of its videos and to keep track of the videos and the clients The Database needs to:
a) b) c) d) e) f) Enable the recording of each loan Provide a quick way of recording returned videos Make sure that no customer hires more than three videos at a time Make it easy to check all the overdue loans Provide a system for sending out reminders Enable the sales staff to check the identity of the hirer and to prevent people hiring videos inappropriate for their age g) Keep track of the usage of each video, so that stock can be replaced in time and to enable strategic decisions about what type of video it is best to stock. h) Record the preferences of customers so that targeted advertising can be sent out advising customers of new stock that they might enjoy.
i) Enable the ordering of new stock from regular suppliers j) Automatically suggesting the replacement of each video that has been hired more than 100 times k) Automatically suggesting the purchase of additional copies of videos which are on loan more than 60% of the time. l) Providing charts to show the relative usage of different types of video m) Provide a feature enabling clients to reserve a video which is out on loan. Consider how his would work… the video is barred from being loaned to anyone for a further three days after its return might be one option, A flag alerting the sales staff when a video is returned that it is reserved, if it is then they would keep it away from the display shelves for a few days.
Have a look at the two example databases that have had tables created within them. Are they structured in a way which would enable all these features to be implemented? Working with a partner, do this exercise: 1. Write a list of all the fields that you will need to implement these features. 2. Go through the list and do first, then second and then third normalisation. i. First Normalisation. Remove all repeating groups, eg situations where one customer hires a number of different videos ii. Second Normalisation. Ensure that al attributes are functionally dependent on the primary key for each entity. Where there are a number foreign keys in an entity then all other attributes must be functionally dependent on all of the foreign keys. eg in the loans table only include attributes which are dependent on both the customer and the video keys iii. Third normal form: Ensure that no two non-identifying attributes are functionally dependent on each other. eg Publisher name and publisher contact 3. Draw an entity relationship diagram 4. a – m Think about each of the features in the list of functions above and decide what forms, reports and queries you will need for each one. Draw a sketch diagram of each form or report and list all the attributes that you will need to include in each query. 5. Draw a flow diagram to show the process of loaning a video 6. Draw a flow diagram for the process of reserving a video
Tim Hardingham a6ee9374-63c7-4290-9c3d-056e186f3793.doc 11/02/09