The relational database below contains two related tables - Supplier and Product: Product table: Primary key =
Question:
The relational database below contains two related tables - “Supplier” and “Product”:
“Product” table: Primary key = Prod-code, Foreign key = Supp-code
Prod-code | Prod -name | Prod -desript | Prod -stocktype | Prod -stocklevel | Prod -expireDate | Supp-code |
10010 | aaa | qwert | 615 | 123445677 | 2017-11-14 | 501x |
10011 | bbb | asdfgg | 615 | 234567899 | 2017-11-16 | 501x |
10012 | ccc | zxxcvv | 234 | 345678900 | 2017-11-13 | 502x |
10013 | eee | ytuytu | 234 | 454577777 | 2017-10-13 | 503x |
10014 | jjj | werewr | 231 | 436436885 | 2017-10-23 | 503x |
10015 | sdg | rtyrtyry | 275 | 676446322 | 2017-10-30 | 504x |
10016 | ewt | erterter | 789 | 232317879 | 2017-12-30 | 501x |
“Supplier” table: Primary key = Supp -code, No Foreign key
Supp -code | Supp -lname | Supp -fname | Supp -Initial | Supp -areacode | Supp -phone |
501x | xxx | qwert | F | 615 | 123445678 |
502x | yyy | asdfgg | B | 615 | 234567890 |
503x | zzz | zxxcvv | X | 234 | 345678901 |
- From the above data, what type of relationships: one-to-one, one-to-many, or many-to-many, do the above two tables have? Explain your answer or draw the entity relationship diagram for these 2 tables. (2 marks)
- The above database contains an error in the data. Identify and describe this error. (3 marks)
- Write a Query-by-example (QBE) to display the Prod-code, Prod-name, Prod-descript for those products that are supplied by supplier with Supp-code “502x”. Sort the display results by Prod-name in ascending order. Use the table below for your answers. (5 marks)
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George