database management system

What you are required to do.

 

Phase 1: Investigation and Planning

  1. Analyse the information that you have been provided about the library’s data requirements. Identify entities, attributes, and relationships.

 

Phase 2: Database Design

  1. You will create an Entity-Relationship (ER) diagram for the community library scenario.
  2. Design a data dictionary for the library, specifying entities, attributes, and data types.
  3. Ensure that your data is in 3rd Normal Form so that you can create your database.

 

Phase 3: Database Implementation

  1. You will use Microsoft Access Relational Database Management System (RDBMS) to implement your new relational database for the Community Library.
  2. You must create the necessary tables for the library based on the ER diagram and data dictionary that you created in Phase 2.
  3. Implement primary keys, foreign keys, and other constraints.
  4. Populate the tables with the sample data that has been provided to you.

 

 

 

Phase 4: SQL Queries for Data Output

 

  1. Perform an SQL Query that will retrieve all details about all the books currently available for loan.
  2. Perform an SQL Query that will retrieve all details about all the patrons who borrow books from the library.
  3. Perform an SQL Query that will retrieve all details about all the current borrowings of the library.
  4. Perform an SQL Query that will retrieve books that have been borrowed by John Smith.
  5. Perform an SQL Query that will retrieve all books that were currently being borrowed as at the close of business on 01 Jun 2023.
  6. Perform an SQL Query that will retrieve the number of books borrowed by each patron.
  7. Perform an SQL Query that will retrieve the details of the most borrowed book.
  8. Perform an SQL Query that will retrieve the details of the patron/s who have borrowed the most books.

 

Phase 5: Documentation and Presentation

  1. You must provide a report which will include:
  2. Cover page
  3. Entity Relationship Diagram
  4. Data Dictionary
  5. Steps showing the normalisation process.

 

  1. You are required to produce a copy of your completed Access Database which will include:
  2. Your tables
  3. Your SQL Queries
  4. Your ERD in software format

 

 

What needs to be submitted    

Completed

ER Diagram

Data Dictionary

Steps taken to normalise your data

Completed Microsoft Access Database

CHECK FOR ALL THIS AT THE END OF THE PROJECT!!!!!!

ENTITIES:

Correctly names all entities –

ATTRIBUTES:

Shows all attributes for each entity –

Shows most attributes for each entity –

Shows limited attributes for each entity –

KEYS:

All primary keys correctly named –

All foreign keys correctly named

RELATIONSHIPS:

All relationships correctly noted with crow’s foot notation –

PRESENTATION:

ERD is presented correctly with crow’s foot notation added for all entities –

DATA DICTIONARY

Describes fully all constraints with appropriate data types, validation rules, or uniqueness requirements present in the data. –

Describes most constraint with appropriate data types s, validation rules, or uniqueness requirements present in the data. –

Describes some constraints with appropriate data types, validation rules, or uniqueness requirements present in the data. –

DATA NORMALIZATION      

Documentation of normalisation process:

Clear explanation of normalisation steps:

DATABASE IMPLEMENTATION

Correct implementation of tables and attributes:

Accurate use of primary and foreign keys:

Properly populated tables with provided data:

Implementation of constraints and indexes:

SQL INQUIRES

Correct and functional SQL queries:

Use of appropriate SELECT, JOIN, and WHERE clauses:

Proper use of aggregate functions (COUNT, AVG, etc.):

DOCCUMENTATION

Comprehensive documentation including ER diagram, data dictionary, SQL queries: