1 / 10

Logical Modelling Exercises Part 2

Logical Modelling Exercises Part 2. Answers?. Explain the database anomalies in the following table:. Anomalies?. What is the Primary Key? Let us asssume it is Project_Code . Insert: Need to know details of Project & Staff & Pay in order to insert a ‘complete’ new record .

naida-gibson
Download Presentation

Logical Modelling Exercises Part 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Logical Modelling ExercisesPart 2 Answers?

  2. Explain the database anomalies in thefollowing table:

  3. Anomalies? • What is the Primary Key? • Let us asssume it is Project_Code. • Insert: • Need to know details of Project & Staff & Pay in order to insert a ‘complete’ new record. AND • I will have to insert redundant data if a member of staff is allocated to multiple projects. • Delete: • If I delete the record for ‘White’ I will also loose the Pay_Scale details for Grade ‘B2’. • Update: • If the Pay_Scale for Grade ’A2’ changes to ‘5’, I have to remember to change multiple records.

  4. What would a ‘better’ set of tables (relations?) to store the data look like? Staff Project Pay

  5. 2 ABC manages a customer purchasing system for which you must develop a Database Schema (i.e. Logical Data Model), based upon the following ER Diagram. • NB. • There is NO Many-to-Many relationship between Customer & Order.

  6. Schema: CustOrdProd • Domains: • BigNumbers: Integer, >0. • MediumNumbers: Integer, >0, <1000 • SmallNumbers: Integer, >0, <=100. • Names: Text, Title Case, <= 35 Characters. • GeneralText: Text, <=25 Characters. • PostCodes: Text, Upper Case, >= 7 & <= 8 Characters. • TelephoneNumbers: Text, [0-9], >= 6 & <= 15 Characters. • Prices: Currency, £, >10 & <=250, 2 Decimal Places. • LongDates: Date, dd/mmm/yyyy.

  7. Relation: Customer • ID: BigNumbers; • Name: Names; • Street: GeneralText; • Town: GeneralText; • PostCode: PostCodes; • TelNo: TelephoneNumbers; • Primary Key: ID

  8. Relation: Order • ID: BigNumbers; • CustID: BigNumbers; • Date: LongDates; • Primary Key: ID • Foreign Key: CustID references Customer.ID

  9. Relation: Product • ID: BigNumbers; • Name: Names; • Description: GeneralText; • CostPrice: Prices; • SalePrice: Prices; • QtyInStock: MediumNumbers; • Primary Key: ID

  10. Relation: ProdOrd • OrderID: BigNumbers; • ProdID: BigNumbers; • QtyOrderd: SmallNumbers; • PricePaid: Prices; • Primary Key: OrderID & ProdID • Foreign Key: OrderID references Order.ID • Foreign Key: ProdID references Product.ID

More Related