Explain the concepts and architectures involved in the database development.
Using your own world, differentiate between database schema and database state with examples.
2 Marks
Learning Outcome(s): CLO3
Create Entity-Relationship model, Relational model, and write SQL queries
Question Two
The advent of online shopping has revolutionized the traditional shopping experience, offering convenience to consumers worldwide. However, have you ever pondered the intricate processes that unfold once you confirm your purchase? Beyond the seamless transaction lies a sophisticated infrastructure comprising databases, servers, and applications working in unison.
Provide the relational database schema for an online shopping system where customers place orders. An order may consist of multiple products, with products categorized for easy organization and search. The Customer table identifies the customers placing orders with their personal information, while the Category table specifies the product categories and description. The Product table lists the available items for purchase and their prices, each linked to a category through the Category ID. Orders, recorded in the Order table with date of order and the total amount, and they are associated with customers through the Customer ID. The Order Detail table captures the intricate relationship between orders and products, detailing the products included in an order along with their respective quantities.
2 Marks
Learning Outcome(s):
CLO3: Create Entity-Relationship model, Relational model, and write SQL queries.
Question Three
Compare and contrast the effects of specialization and generalization on the hierarchy of Enhanced Entity-Relationship (EER) diagrams. Give examples to illustrate how generalization and specialization differ.
2 Marks
Learning Outcome(s):
CLO 3
Instructors: Create Entity-Relationship model, Relational model, and write SQL queries
Question Four
Draw an ER diagram for the following SPL Saudi Post system.
SPL application relies on a company-wide information system. In SPL Saudi Post system, shipped items are the main component of the SPL product tracking information system. Shipped items can be characterized by item number (unique), weight, dimensions, insurance amount, destination, and final delivery date. Shipped items are received into the SPL system at a single retail center. Retail centers are characterized by their type, unique ID, and address. Shipped items make their way to their destination via one or more standard SPL transportation events (flights, truck deliveries). These transportation events are characterized by a unique schedule Number, a type (flight, truck), and a delivery Route.