Data Modeling Exercise.

You are assigned a project as a DBA to design a data model to support a library system. At a minimum, the database needs to support tracking the books in the library inventory, the book author(s), the book publisher, and any optional associated book categories. You will need to make some assumptions as to what attributes are associated with each entity. For example, you may decide that the Author entity will require Author ID, FirstName and LastName attributes.

Note:

A book can have 1 or more authors

A book can only have 1 publisher

A book can have 0 or more category

For this assignment, create the conceptual, logical and physical data models using draw.io or Visio. Use the following guide on what to include in each model:

Conceptual

Logical

Physical Data Models

(https://cdn.discordapp.com/attachments/475892445746298890/493939491933913088/Conceptual_Logical_and_Physical_Data_Models.PNG) [link example]

Important: do not include more entities than required. Only design to meet the specified requirements.

Denormalization exercise

Review the small data model shown in Figure 4.5 on page 182. Document a denormalization change to the data model to support the following fictitious reporting requirement:

(https://cdn.discordapp.com/attachments/475892445746298890/493939609546391554/4.5_pg182.jpeg)[link]

The number of course credits a student is current enrolled is almost always needed when querying the student record. As a result, the request has been made to improve performance of this query by using derivable data column.