Hi guys,

Just like to check if I'm on the right lines, been given a scenario for a college project to create a database for a rental store, we have to create an ER diagram and map it as well as normalize, implement etc.

Been struggling with this so far, however I've done my ER and mapping (attached and below) and would just like to check if its on the right lines?


Also when I've came to normalize it, I'm struggling to make any changes, am i not looking for the correct things or have I normalized it without realizing!

Thanks for your help

AD

Hey, just wondering if someone could check my normalization up to 3NF, I had the following from Mapping to a DBMS

And my normalization is the following, I cant see any changes to make in the 3NF, I dont know if I've got completely the wrong idea

Thanks

AD

do you need to subtype the branch telephone number, likewise supplier.. do suppliers and or branches have multiple numbers?
customer telephone number I could see if you classified what type of phone number it is (eg work home, mobile and so on)

strictly speaking is a customer 'owned' by a specific branch or are they just a customer, who happens to have used a branch (you can derive waht branch a customer has used through what equipment they have used.

if you are a UK user then I'd argue your address entities need an extra line, some UK addresses have 5 lines..

surprised theres no enitity to describe what genre is

all in all looks a reasonable attempt +1

Thanks healdem, yeah I was thinking that for the branch and supplier numbers, the im slightly confused about, the idea was to have


Would it be better to have a type table with DVD, CD, blu-ray etc and then another with the genre in?

Also would this be 1NF? Repeating Elements etc?

if the organisation only rents then as I see it
it rents 'stuff' on specific media
that media could be, say CD,LP,DVD, BluRay and so on
if its possible that the same 'stuff' could be on more than one type of media
(eg there could be a DVD, VHS, BluRay of the same film) then you need to push the actual instance of the rental copy beneath the description of the item

eg you woudl have a table for products (eg all films, music, games etc that you have)
you then underneath that another table detailing what media that film / music / game is on

you woudl have to decie early on whether say a 3d film is the same product as a 2d film. from the customer perspectivre tey may want to rent, say 'Avatar' and don't really care if its the 2d or 3d version, other customers may want only the 3d version. it comes down as to how you thgink the organisation wants to ineract with its customers

..what do you think?
..do you understand the concepts of normalisation and realtional table design?
Fundamentals of Relational Database Design -- r937.com
The Relational Data Model, Normalisation and effective Database Design


take a step back from the theory and the design you currently have
if you were a customer how would you want to drill down through the rental outlets stock.
do you look for specific actors or directors or year of release
do you look for genres
do you look for ratings
do you look for specific dates (ie what can i rent right here, right now)

one of the main reasons for using realtional db design is to promote and maintain the data.

if you don't follow the realtional model either you as develoepr have to put a lot more effort, time and resources into ensuring the data integrity or the data goes crap pretty quickly as mispellings, typos and poor data entry screw up the system

ferinstance
assume you stored an individual entry per, day film

someone might type in
The Bourne Ultimatum
Bourne Ultimatum
Bourne Ultimatum,the
The bourne ultimatum

all of which are different in a computers 'mind'
instead having a table that identifies the item title, and then another table which records a spoecific instance of a title

eg
table: rentaltitles
1: Avatar: Film: Science Fanatasy......
4: Italian Job, the: Action
table:itemsforrent
1: rentaltitlesID (of 1): DVD
4: rentaltitlesID (of 1): BluRay
3: rentaltitlesID (of 4): DVD
13: rentaltitlesID (of 4): VHS

naturally you wouldn't store DVD, BluRay, and so on in the itemstorent table, you'd use the priamry key form media type for the same reason that dvd,DVD Digital Versitile Disc are not the same in a computer's 'mind'