Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Is this correct so far?

    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?

    Step 1: Mapping of Regular Entity Types

    Customer (CustomerID, FirstName, LastName, Address1, Address2, City, County, Postcode, DOB)
    Branch (BranchID, BranchName, Address1, Address2, City, County, Postcode)
    Supplier (SupplierID, SupplierName, Address1, Address2, City, County, Postcode)

    Step 2: Mapping of Weak Entity Types
    Loan (StartDate, EndDate, CustomerID)
    Item (Name, Type, Genre, SupplierID)

    Step 3: Mapping of Binary 1:1 Relationship Types
    No 1:1 Relationships

    Step 4: Mapping of Binary 1:N Relationship Types

    Loan (LoanID, StartDate, EndDate, CustomerID)
    Customer (CustomerID, FirstName, LastName, Address1, Address2, City, County, Postcode, DOB, BranchID)
    BranchID added to customer to allow a customer to belong to a branch and customerID added to loan to allow a loan to be associated with a customer.

    Step 5: Mapping of Binary M:N Relationship Types
    LoanItems (LoanID, ItemID, ReturnStatus)

    Step 6: Mapping of Multi-valued attributes.
    CustomerTelephone (CustomerID, Telephone)
    SupplierTelephone (SupplierID, Telephone)
    BranchTelephone(BranchID, Telephone)

    Step 7: Mapping of N-ary Relationship Types.
    SupplyInfo (SupplierID, ItemID, BranchID)

    Tables

    Customer (CustomerID, FirstName, LastName, Address1, Address2, City, County, Postcode, DOB, BranchID)
    Branch (BranchID, BranchName, Address1, Address2, City, County, Postcode)
    Supplier (SupplierID, SupplierName, Address1, Address2, City, County, Postcode)
    Item (Name, Type, Genre, SupplierID)
    Loan (LoanID, StartDate, EndDate, CustomerID)
    LoanItems (LoanID, ItemID, ReturnStatus)
    CustomerTelephone (CustomerID, Telephone)
    SupplierTelephone (SupplierID, Telephone)
    BranchTelephone(BranchID, Telephone)
    SupplyInfo (SupplierID, ItemID, BranchID)

    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
    Attached Thumbnails Attached Thumbnails ERD.jpg  

  2. #2
    Join Date
    Apr 2011
    Posts
    3

    Can someone check my normalization?

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

    Customer (CustomerID, FirstName, LastName, Address1, Address2, City, County, Postcode, DOB, branchName)
    Branch (BranchID, BranchName, Address1, Address2, City, County, Postcode)
    Supplier (SupplierID, SupplierName, Address1, Address2, City, County, Postcode)
    Item (ItemID, Name, Type, Genre, SupplierName)
    Loan (LoanID, StartDate, EndDate, CustomerID)
    LoanItems (LoanID, ItemID, ReturnStatus)
    CustomerTelephone (CustomerID, Telephone)
    SupplierTelephone (SupplierID, Telephone)
    BranchTelephone(BranchID, Telephone)
    SupplyInfo (SupplierID, ItemID, BranchID)
    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

    1NF:
    Customer (CustomerID, FirstName, LastName, Address1, Address2, City, County, Postcode, DOB)
    BranchCustomer(BranchID, CustomerID)
    Branch (BranchID, BranchName, Address1, Address2, City, County, Postcode)
    Supplier (SupplierID, SupplierName, Address1, Address2, City, County, Postcode)
    Item (ItemID, Name, Type, Genre)
    ItemBranch(branchID, itemID)
    Loan (LoanID, StartDate, EndDate, CustomerID)
    LoanItems (LoanID, ItemID, ReturnStatus)
    CustomerTelephone (CustomerID, Telephone)
    SupplierTelephone (SupplierID, Telephone)
    BranchTelephone(BranchID, Telephone)
    SupplyInfo (SupplierID, ItemID)

    2NF
    Customer (CustomerID, FirstName, LastName, Address1, Address2, City, County, Postcode, DOB)
    BranchCustomer(BranchID, CustomerID)
    Branch (BranchID, BranchName, Address1, Address2, City, County, Postcode)
    Supplier (SupplierID, SupplierName, Address1, Address2, City, County, Postcode)
    Item (ItemID, Name, Type, Genre)
    ItemBranch(branchID, itemID)
    Loan (LoanID, StartDate, EndDate, CustomerID)
    LoanItems (LoanID, ItemID)
    LoanItemReturns (ItemID, ReturnStatus)
    CustomerTelephone (CustomerID, Telephone)
    SupplierTelephone (SupplierID, Telephone)
    BranchTelephone(BranchID, Telephone)
    SupplyInfo (SupplierID, ItemID)

    3NF
    Customer (CustomerID, FirstName, LastName, Address1, Address2, City, County, Postcode, DOB)
    BranchCustomer(BranchID, CustomerID)
    Branch (BranchID, BranchName, Address1, Address2, City, County, Postcode)
    Supplier (SupplierID, SupplierName, Address1, Address2, City, County, Postcode)
    Item (ItemID, Name, Type, Genre)
    ItemBranch(branchID, itemID)
    Loan (LoanID, StartDate, EndDate, CustomerID)
    LoanItems (LoanID, ItemID)
    LoanItemReturns (ItemID, ReturnStatus)
    CustomerTelephone (CustomerID, Telephone)
    SupplierTelephone (SupplierID, Telephone)
    BranchTelephone(BranchID, Telephone)
    SupplyInfo (SupplierID, ItemID)
    Thanks

    AD

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2011
    Posts
    3
    Thanks healdem, yeah I was thinking that for the branch and supplier numbers, the im slightly confused about, the idea was to have


    Item
    1, The hangover, DVD, Comedy, DVD Supplies
    2, The Bourne Ultimatum, DVD, Action, We r supplies
    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?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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

    Would it be better to have a type table with DVD, CD, blu-ray etc and then another with the genre in?
    ..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'
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •