Scribd
Upload
6 views5 pages

Entity Relationship Model for Bookstore

The document outlines the structure of a database for managing books, customers, orders, shipping, and reviews, detailing the entities, their attributes, primary keys, and foreign keys. It also describes the relationships between these entities, including cardinality and relationship attributes. Additionally, it includes SQL queries and relational algebra expressions to retrieve specific data from the database.

Uploaded by

ahmedhisham6595
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views5 pages

Entity Relationship Model for Bookstore

The document outlines the structure of a database for managing books, customers, orders, shipping, and reviews, detailing the entities, their attributes, primary keys, and foreign keys. It also describes the relationships between these entities, including cardinality and relationship attributes. Additionally, it includes SQL queries and relational algebra expressions to retrieve specific data from the database.

Uploaded by

ahmedhisham6595
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Entity Type Attributes of the Entity Key

Books ISBN, Title, Author, Genre, PublicationDate, Price, ISBN (Primary Key)
QuantityAvailable

Customers CustomerID, Name, ShippingAddress, ContactNumber, Email CustomerID (Primary Key)

Orders OrderID, CustomerID (FK), OrderDate, TotalAmount, OrderID (Primary Key), CustomerID
PaymentStatus (Foreign Key)

Shipping ShippingID, OrderID (FK), ShippingAddress, DeliveryStatus, ShippingID (Primary Key), OrderID
TrackingNumber (Foreign Key)

Reviews eviewID, CustomerID (FK), ISBN (FK), Rating, ReviewText ReviewID (Primary Key),
CustomerID (Foreign Key), ISBN
(Foreign Key)

OrderDetails OrderDetailID, OrderID (FK), ISBN (FK), Quantity OrderDetailID (Primary Key),
OrderID (Foreign Key), ISBN
(Foreign Key)

Relationship Type Entities connected bythe relationship Cardinality Relationship attribute


type

Purchases Customers - Orders 1:N CustomerID (FK)

Contains Orders - OrderDetails 1:N OrderID (FK)

Includes OrderDetails - Books N:1 ISBN (FK)

Ships Orders - Shipping 1:1 OrderID (FK)

Reviews Customers - Reviews 1:N CustomerID (FK)

ReviewedBy Books - Reviews 1:N ISBN (FK)


a) Draw the EER Diagram
Create the tables on SQL server management Studio and populate them with at least 10
rows.(
b) Write SQL queries to find the following:

• Write the relational algebra expression to find:


•
• [Link] who purchased more than 3 books:
• $\sigma_{SUM(Quantity) > 3} (\pi_{Name} (Customers \bowtie Orders \bowtie OrderDetails))$

• [Link] of books in "Fiction" genre ordered by at least one customer:


• $\pi_{Title} (\sigma_{Genre='Fiction'} (Books) \bowtie OrderDetails \bowtie Orders)$

• [Link] and emails of customers who placed orders for more than 5 books:
• $\sigma_{SUM(Quantity) > 5} (\pi_{Name, Email} (Customers \bowtie Orders \bowtie OrderDetails))$

You might also like