Sibusiso has been working at the warehouse of a large online retailer for a while now....

Transcribed Image Text:

Sibusiso has been working at the warehouse of a large online retailer for a while now. He started working there while he was still making up his mind about what to study. Furthermore, he still had to save up to pay for his studies. He was determined to learn everything that there was to learn. He was working in fulfilment, carefully packing items into boxes before they were shipped all over the country. It was fun because he was always imagining what the look on the face of the recipient would be when they opened the box. Especially when the order was marked as a gift, and he got to gift wrap it. His favourite order ever was a giant pink teddy bear. It took some time to find a way to gift wrap it, but the end-product did not look like a bear. So, mystery preserved for a minute more before the gift wrap would be torn off by eager little hands. Best. Day. Ever! As much fun as that was, though, Sibusiso knew his future was in IT. The team upstairs that were building and maintaining the e-commerce website was the most important group of people in the whole company in his mind. Sure, you need fulfilment and procurement as well as human resources, finance, and marketing. However, without the website, the customers will not be able to place orders as a result nobody else would have anything to do. Whenever Sibusiso took a break, he was on the lookout for any group of IT people he could join to listen to what they were discussing. He soon realised that the database designers are the most interesting to him. They were always discussing ways to improve how data was stored and retrieved. That appealed to Sibusiso because he loved to create order out of chaos. Will you help him to learn more about database design? In the database design world, you must know many terminologies to understand the conversations of experts in the field. Help Sibusiso to gain this knowledge by answering the questions below. Note: Your answers should be written so that they would make sense to Sibusiso. Marks will be awarded for this. Q.1.1 Describe three tasks that are performed by a database administrator (DBA). Include (6) a relevant example of each one. Q.1.2 A database designer complained that the inventory manager did not understand what he was trying to communicate about the database. Explain why a graphical data model could bridge this communication gap. Q.1.3 Sibusiso found this diagram scribbled on the break room whiteboard. He could understand some of it, but he wanted to know more. Write down any two business rules represented in the below Entity Relationship Diagram (ERD) and explain how you arrived at each of the rules. Customer CustomerID (PK) Name Surname Address ItemID (PK) Name Description Item 11 11 places contains includes 1." 11 Date 1.* 1 Order OrderID (PK) CustomerID (FK) Orderitem OrderitemID (PK) OrderID (FK1) ItemID (FK2) Quantity UnitPrice (4) (6) Q.1.4 A ternary relationship is a concept that junior database designers seem to struggle with. Questions around this often arise in the break room. Give an example of a ternary relationship (in words) and explain why it is a ternary relationship. Question 2 (Marks: 10) The below checklist assists in evaluating whether an Entity Relationship Diagram (ERD) is accurately drawn according to business rules. Phase 5 - Checklist Are all the names of the entities directly from the rules (or composite entities)? Are any of the nouns in the rules missing as entities in the ERD? Are all the relationships from the rules in the ERD? Do all the relationships have names and directions? Do all the relationships have the correct multiplicities? (4) Do the entities all have primary keys (PKs)? Is there a foreign key (FK) for each relationship? Are any of the attributes that are apparent from the rules missing? Source: https://www.pellissier.co.za/wp-content/uploads/erds/content/index.html#/lessons/ Cs381UGO TXHWxkQOKfip1TzvTLblwgs (Accessed 5 February 2023). Given the following business rules, suggest two related changes to be made to the below ERD. Draw a new version of the diagram with the changes clearly indicated and motivate why the changes should be made. Hint: Look at the mark allocation for the question to determine how detailed the answer should be. Business rules: A supplier supplies many different products, but each product is supplied by exactly one supplier. Each product belongs to a specific category, and many products can belong to the same category. Manufacturer . ManufacturerID (PK) . Name . Name Category Tip: Pay attention to the mark allocation shown below. Cat competition business rules: supplies Question 3 (Marks: 25) Draw an Entity Relationship Diagram (ERD) using Unified Modelling Language (UML) notation according to the below business rules. Your design should be at the logical level. Include primary and foreign key fields and remember to remove any many-to-many relationships. 1 Product ProductID (PK) ManufactureriD (FK) Name Description All entities must have surrogate primary keys. Each cat belongs to one specific breed, and many cats can belong to the same breed. Each owner owns one or more cats, and every cat has exactly one owner. A cat can enter many competitions, and many cats can enter the same competition. Sibusiso has been working at the warehouse of a large online retailer for a while now. He started working there while he was still making up his mind about what to study. Furthermore, he still had to save up to pay for his studies. He was determined to learn everything that there was to learn. He was working in fulfilment, carefully packing items into boxes before they were shipped all over the country. It was fun because he was always imagining what the look on the face of the recipient would be when they opened the box. Especially when the order was marked as a gift, and he got to gift wrap it. His favourite order ever was a giant pink teddy bear. It took some time to find a way to gift wrap it, but the end-product did not look like a bear. So, mystery preserved for a minute more before the gift wrap would be torn off by eager little hands. Best. Day. Ever! As much fun as that was, though, Sibusiso knew his future was in IT. The team upstairs that were building and maintaining the e-commerce website was the most important group of people in the whole company in his mind. Sure, you need fulfilment and procurement as well as human resources, finance, and marketing. However, without the website, the customers will not be able to place orders as a result nobody else would have anything to do. Whenever Sibusiso took a break, he was on the lookout for any group of IT people he could join to listen to what they were discussing. He soon realised that the database designers are the most interesting to him. They were always discussing ways to improve how data was stored and retrieved. That appealed to Sibusiso because he loved to create order out of chaos. Will you help him to learn more about database design? In the database design world, you must know many terminologies to understand the conversations of experts in the field. Help Sibusiso to gain this knowledge by answering the questions below. Note: Your answers should be written so that they would make sense to Sibusiso. Marks will be awarded for this. Q.1.1 Describe three tasks that are performed by a database administrator (DBA). Include (6) a relevant example of each one. Q.1.2 A database designer complained that the inventory manager did not understand what he was trying to communicate about the database. Explain why a graphical data model could bridge this communication gap. Q.1.3 Sibusiso found this diagram scribbled on the break room whiteboard. He could understand some of it, but he wanted to know more. Write down any two business rules represented in the below Entity Relationship Diagram (ERD) and explain how you arrived at each of the rules. Customer CustomerID (PK) Name Surname Address ItemID (PK) Name Description Item 11 11 places contains includes 1." 11 Date 1.* 1 Order OrderID (PK) CustomerID (FK) Orderitem OrderitemID (PK) OrderID (FK1) ItemID (FK2) Quantity UnitPrice (4) (6) Q.1.4 A ternary relationship is a concept that junior database designers seem to struggle with. Questions around this often arise in the break room. Give an example of a ternary relationship (in words) and explain why it is a ternary relationship. Question 2 (Marks: 10) The below checklist assists in evaluating whether an Entity Relationship Diagram (ERD) is accurately drawn according to business rules. Phase 5 - Checklist Are all the names of the entities directly from the rules (or composite entities)? Are any of the nouns in the rules missing as entities in the ERD? Are all the relationships from the rules in the ERD? Do all the relationships have names and directions? Do all the relationships have the correct multiplicities? (4) Do the entities all have primary keys (PKs)? Is there a foreign key (FK) for each relationship? Are any of the attributes that are apparent from the rules missing? Source: https://www.pellissier.co.za/wp-content/uploads/erds/content/index.html#/lessons/ Cs381UGO TXHWxkQOKfip1TzvTLblwgs (Accessed 5 February 2023). Given the following business rules, suggest two related changes to be made to the below ERD. Draw a new version of the diagram with the changes clearly indicated and motivate why the changes should be made. Hint: Look at the mark allocation for the question to determine how detailed the answer should be. Business rules: A supplier supplies many different products, but each product is supplied by exactly one supplier. Each product belongs to a specific category, and many products can belong to the same category. Manufacturer . ManufacturerID (PK) . Name . Name Category Tip: Pay attention to the mark allocation shown below. Cat competition business rules: supplies Question 3 (Marks: 25) Draw an Entity Relationship Diagram (ERD) using Unified Modelling Language (UML) notation according to the below business rules. Your design should be at the logical level. Include primary and foreign key fields and remember to remove any many-to-many relationships. 1 Product ProductID (PK) ManufactureriD (FK) Name Description All entities must have surrogate primary keys. Each cat belongs to one specific breed, and many cats can belong to the same breed. Each owner owns one or more cats, and every cat has exactly one owner. A cat can enter many competitions, and many cats can enter the same competition.