8.1 Database Concepts (3)
Resources |
Revision Questions |
Computer Science
Login to see all questions
Click on a question to view the answer
1.
Explain the difference between an Entity-Relationship Diagram (ERD) and a relational database schema. Describe the purpose of an ERD and provide an example of a simple ERD for a library database. Then, explain how a relational database schema is derived from an ERD.
An Entity-Relationship Diagram (ERD) is a visual representation of the entities (real-world objects or concepts) and the relationships between them in a database. It's a high-level design tool used to conceptualize the database structure. A relational database schema, on the other hand, is a detailed blueprint of the database, specifying the tables, columns, data types, primary keys, and foreign keys. It's the actual implementation plan for the database.
Purpose of an ERD:
- To provide a visual overview of the database structure.
- To facilitate communication between stakeholders (e.g., database designers, developers, and users).
- To identify potential data requirements and relationships.
- To serve as a basis for designing the relational database schema.
Example ERD for a Library Database:
| Entity | Attributes |
| Book | BookID, Title, Author, ISBN |
| Member | MemberID, Name, Address, Phone |
| Loan | LoanID, BookID, MemberID, LoanDate, DueDate |
In this ERD, the entities are Book, Member, and Loan. The relationships are represented by the lines connecting the entities. For example, a Book can be borrowed by a Member (represented by the relationship between Book and Loan). The attributes are listed within each entity.
Deriving a Relational Database Schema from an ERD:
- Each entity in the ERD becomes a table in the relational database.
- Attributes of the entity become columns in the table.
- The primary key of the entity becomes the primary key of the table.
- Relationships between entities are implemented using foreign keys. For example, the Loan table would have foreign keys referencing the Book and Member tables to represent the relationships between them.
2.
Question 2: Consider a database table called OrderDetails with the following attributes: OrderID (Primary Key), ProductID (Foreign Key referencing Products), Quantity, and UnitPrice. Explain how this table violates 3NF. Then, describe the steps required to decompose this table into two tables that are in 3NF. Include the primary and foreign keys for each new table.
Answer:
The OrderDetails table violates 3NF because the City attribute (assuming the customer's city is associated with the order) has a transitive dependency on the primary key OrderID. The city depends on the customer's address, and the address depends on the order details (OrderID). This violates 3NF.
To decompose the table into two tables in 3NF, we would create two new tables:
Table 1: OrderDetails
- OrderID (Primary Key)
- ProductID (Foreign Key referencing Products)
- Quantity
- UnitPrice
Table 2: OrderCustomer
- OrderID (Foreign Key referencing OrderDetails)
- City
3.
Question 1
A local library wants to design a database to manage its books, members, and loans. Describe an Entity-Relationship (E-R) diagram to model this system. Your diagram should clearly identify the entities, their attributes (including primary keys), and the relationships between them. Explain the cardinality and optionality of each relationship. Consider the following requirements:
- Each book has a unique ISBN.
- Each library member has a unique membership number.
- A book can be borrowed by multiple members.
- A member can borrow multiple books.
- A book can be written by one or more authors.
- A book can be of multiple genres.
Entities:
- Book: Attributes: ISBN (PK), Title, Publication Year, Genre (FK to Genre entity)
- Member: Attributes: Membership Number (PK), Name, Address, Phone Number
- Author: Attributes: Author ID (PK), Name, Nationality
- Genre: Attributes: Genre ID (PK), Name
- Loan: Attributes: Loan ID (PK), Book (FK to Book entity), Member (FK to Member entity), Loan Date, Due Date, Return Date (nullable)
Relationships:
- Book 1 Author: Many-to-many relationship. Implemented with a linking table BookAuthor.
BookAuthor Attributes: Book (FK to Book entity), Author (FK to Author entity), Role (e.g., 'Author', 'Illustrator')
- Book 1 Genre: Many-to-many relationship. Implemented with a linking table BookGenre.
BookGenre Attributes: Book (FK to Book entity), Genre (FK to Genre entity)
- Member 1 Loan: One-to-many relationship. A member can have multiple loans.
Cardinality: 1:N
- Book 1 Loan: One-to-many relationship. A book can be borrowed multiple times.
Cardinality: 1:N
Diagram (Conceptual):
A diagram would show rectangles for each entity, ovals for attributes, and diamonds for relationships. Lines would connect the entities, with symbols indicating cardinality (e.g., crow's foot for 'many', single line for 'one'). Relationships would be labelled with the relationship name (e.g., "Borrows").