71  Database design answers

71.1 Retail Screen and its answer

Retail Data Enterance

taken from graphic design stack exchange

71.1.1 Retail Screen Answer

erDiagram
    Client  o|--|{ Invoice : "has"
    Invoice ||--|{ InvoiceLine : "has"
    Product ||--|{ InvoiceLine : "has"
    User  o|--|{ Invoice : "has"

    User{
        int userId
        string name
        string password
        string photoUrl
    }
    Client{
        string id
        string name
        string address
        string phone
        string email
    }
    Product{
        int productId
        string productRef
        string productCode
        string title
        string price
        string color
    }
    Invoice{
        int invoiceId
        int clientId
        int userId
        float total
    }
    InvoiceLine{
        int invoiceLineId
        int invoiceId
        int productId
        int count
        float discount
        float subtotal
        float VAT
        float subtotal
    }

71.1.2 Subscription Answer

subscription

The ER Model for the answer is below.

internet_domain-er-model

You could also download sqlite database

71.1.3 Student Advisors

erDiagram
    Department ||--|{ Student : has
    Building ||--|{ Office : has
    Office ||--|{ Advisor_Office : has
    Advisor_Office ||--|{ Advisor : in 
    College  ||--|{ Department : has
    Advisor ||--|{ Student: has_multiple

    Student{
        string StudentNum
        string name
        string major
        float gpa
        string class
    }
    Department{
        string code
        string name
        string phone
    }
    College{
        string CollegeId
        string name
    }
    Advisor{
        string advisorId
        string name
        string phone
        string office
        int buildingId
    }
    Advisor_Office{
        int advisorId
        int officeId
    }
    Office{
        int officeId
        string code
        int buildingId
    }
    Building{
        int buildingId
        string buildingName
    }