Database Design And Development Assignment Sample

Part 1: Database Design and Development

Task 1: Coffee Shop Relational Database Design

i. Logical Diagram

Figure 1: Logical Diagram

Customer: This table stores the details of Someone who orders from the coffee shop.

Order: This table stores each order details placed by a customer.

Item: This table stores different item details including coffee varieties, biscuits, cakes, etc.

Size: This table stores different item sizes (e.g., small, medium, large).

Order_Items: This table acts as a linking table between Order and Item.

Assumptions

Each order can have multiple items, and each item can belong to multiple orders.

Each item has multiple sizes and prices, depending on the size.

ii. Physical Diagram

Figure 2: Physical Diagram

In this part the logical table is modified with data types and key types of the attributes of tables.

Entities and their attributes

1. CUSTOMER

• Customer_ID (SMALLINT, PK)
• First_Name (VARCHAR(50), NOT NULL)
• Last_Name (VARCHAR(50), NOT NULL)
• Contact_Number (BIGINT, UNIQUE)
• Email (VARCHAR(100))

1. ORDER

• Order_ID (SMALLINT, PK)
• Order_Date (DATE, NOT NULL)
• Customer_ID (SMALLINT, FK, NOT NULL)
• Total_Price (DECIMAL(10,2), NOT NULL)

1. ITEM

• Item_ID (SMALLINT, PK)
• Item_Name (VARCHAR(20), NOT NULL)
• Item_Type (VARCHAR(20), NOT NULL)
• Size_ID (SMALLINT, FK, NOT NULL)

1. ORDER_ITEMS

• Order_Item_ID (SMALLINT, PK)
• Order_ID (SMALLINT, FK, NOT NULL)
• Item_ID (SMALLINT, FK, NOT NULL)
• Quantity (TINYINT, NOT NULL)

1. SIZE

• Size_ID (SMALLINT, PK)
• Size_Name (VARCHAR(20), NOT NULL)
• Price (DECIMAL(10,2), NOT NULL)

Relationships

• A customer can place multiple orders.
• An order can contain multiple items, and an item can be included in multiple orders.
• An item belongs to a specific size.

Constraints

• FK_Order_Customer: This constraint connects the ORDER table's Customer_ID to the CUSTOMER table's Customer_ID.
• FK_Item_Size: This constraint connects the ITEM table's Size_ID to the SIZE table's Size_ID.
• FK_OrderItems_Order: This constraint connects the ORDER_ITEMS table's Order_ID to the ORDER table's Order_ID.
• FK_OrderItems_Item: This constraint connects the ORDER_ITEMS table's Item_ID to the ITEM table's Item_ID.

Task 2: Create the Library Relational Database

In the allocated platform using the phpmyadmin the library database has been created based on the library physical er model diagram. To avoid any complexities initially the tables are created and then the foreign keys are added to the tables using an alter table.

New Assignment Help provides high-quality Database Design And Development Assignment Help tailored to your university guidelines. Get started today!

a. Database creation

Figure 3: Address, Member and Publisher table creation

The figure 3 shows the address, member and publisher table creation code.

Figure 4: Book, Author and Book_Authors table creation

The figure 4 shows the book, author and book_authors table creation code.

Figure 5: Transaction table creation

Figure 5 shows the transaction table creation code.

Figure 6: Successful execution output for table creation

The table creation code executed successfully which is shown in the above figure 6.

Figure 7: Foreign keys addition to the tables

After tables creation the foreign keys are added as constraints using an alter table statement.

Figure 8: Successful execution output of the foreign keys addition

The above figure shows the successful execution of adding the foreign keys constraints to the database.

b. Sample data insertion

After creating all the tables and adding the foreign keys sample data inserted to the tables.

Figure 9: Sample data insertion in the address table

Five sample data inserted to the address table including address id, door number, street name, city and postcode.

Figure 10: Successful execution of data insertion to the address table

The above figure shows the successful execution of the sample data insertion to the address table.

Figure 11: Eight sample data insertion in the member table

Eight sample data inserted to the member table including member last name, mobile number and address id. The member id is automatically incremented based on the data\

insertion.

Figure 12: Successful execution of data insertion to the member table

The above figure shows the successful execution of the sample data insertion to the member table.

Figure 13: Sample data insertion in the Publisher table

Five sample data inserted to the publisher table including publisher id, publisher name, and publisher location.

Figure 14: Successful execution of data insertion to the publisher table

The above figure shows the successful execution of the sample data insertion to the publisher table.

Figure 15: Twelve sample data insertion in the book table

Twelve sample data inserted to the book table including book title, genre, publisher details, publisher year and number of copies. The book id is automatically incremented based on the data insertion.

Figure 16: Successful execution of data insertion to the book table

The above figure shows the successful execution of the sample data insertion to the book table.

Figure 17: Sample data insertion in the author table

Eleven sample data inserted to the author table including address id, door number, street name, city and postcode. The author id is automatically incremented based on the data insertion.

Figure 18: Successful execution of data insertion to the author table

The above figure shows the successful execution of the sample data insertion to the author table.

Figure 19: Sample data insertion in the Book_Authors table

Twelve sample data inserted to the book author table including book id, author id.

Figure 20: Successful execution of data insertion to the book_authors table

The above figure shows the successful execution of the sample data insertion to the book_authors table.

Figure 21: Ten sample data insertion in the transaction table

Ten sample data inserted to the transaction table including book id, member id, issue date, expected return date, and actual date. The transaction id is automatically incremented based on the data insertion.

Figure 22: Successful execution of data insertion to the transaction table

The above figure shows the successful execution of the sample data insertion to the transaction table.

c. Tables after data insertion

Figure 23: Address Table after data insertion

The above figure shows the address table after successful data insertion.

Figure 24: Member Table after data insertion

The above figure shows the member table after successful data insertion.

Figure 25: Publisher Table after data insertion

The above figure shows the publisher table after successful data insertion.

Figure 26: Book Table after data insertion

The above figure shows the book table after successful data insertion.

Figure 27: Author Table after data insertion

The above figure shows the author table after successful data insertion.

Figure 28: Book_Authors Table after data insertion

The above figure shows the book authors table after successful data insertion.

Figure 29: Transaction Table after data insertion

The above figure shows the transaction table after successful data insertion.

Part 2: Test the Library Relational Database

After database creation and sample data insertion the database is tested by querying based on different test cases.

Task 3: Solutions for different cases

In this part different test cases and their possible sql solutions are checked. 

Case 1: Search

a. Transaction details based on book issue date

Figure 30: Query to show the transaction details based on book issue date

The query helped to show all the details of a transaction based on a certain issue date.

Figure 31: Transaction details based on book issue date

Here is the output of running the query to show the transaction details with issue date “2024-08-01”.

b. Member details based on contact Number

Figure 32: Query to show the member details based on mobile number

The query helped to show all the details of a member based on a certain mobile number.

Figure 33: Member details based on mobile number

Here is the output of running the query to show the member details with mobile number “1234567890”.

c. Book details based on book title

Figure 34: Query to show the Book details based on title

The query helped to show all the details of a book based on a book title.

Figure 35: Book details based on title

Here is the output of running the query to show the book details with title “War and Peace”.

Case 2: Data manipulation

a. Deletion of a damaged book

Figure 36: Query to delete a damaged book

The query helped to delete a damaged book from the database. Initially the book was deleted from the book authors table, then from the transaction table and finally from the book table. This way all the existence of the book is completely removed from the database.

Figure 37: Successful deletion of a damaged book

The above figure shows the successful execution of the damaged book deletion query based on the book id “8”.

b. Updation of contact number of a certain member

Figure 38: Query to update contact number of a certain member

The query helped to update a member’s contact number with a new contact number based on the last name of the member.

Figure 39: Successful updation of contact number of a certain member

The above figure shows the successful execution of the contact number updation query based on the member last name “Smith”.

Case 3: Appreciation System

a. Addition of Comments and Appreciation Points column in the Transaction table

Figure 40: Query to add new columns to the transaction table

The query helped to add two new columns named comments and appreciation points to the transaction table.

Figure 41: Successful execution of the new columns addition

The above figure shows the successful execution of the new columns addition query.

Figure 42: Updating the comments column with sample data

After adding the columns using update statement sample data inserted to the comments column.

Figure 43: Successful execution of the sample data insertion to the comments column

The above figure shows successful execution of the sample data insertion to the comments columns.

Figure 44: Updating the appreciation points column based on comments column

The query helped to update the appreciation points column based on the sample data inserted to the comments column.

Figure 45: Successful updation of the appreciation points column

The above figure shows successful execution of the updation query of the appreciation points column.

Figure 46: Transaction table after new columns creation and data insertion

The above figure shows the transaction table after adding two new columns and data insertion to them.

b. Order of members based on total appreciation points

Figure 47: Query to show the order of members based on total appreciation points

The query helped to show the list of members in an order of highest to lowest based on total appreciation points.

Figure 48: Order of members based on total appreciation points

The output of the query with the list of members ordered based on the total appreciation points.

Case 4: Statistics

a. Total members in a specific month

Figure 49: Query to show the total active members of a specific month

The query helped to show the total number of members visited the library in a certain month.

Figure 50: Total members of the month 8(August)

The output of the query shows based on the month number 8 (August). According to the output in the month, a total of seven members visited the library.

Reference List

Journals

• Beaulieu, A., 2020. Learning SQL: Generate, manipulate, and retrieve data. O'Reilly Media.
• DeBarros, A., 2022. Practical SQL: A Beginner's Guide to Storytelling with Data. no starch Press.
• Rockoff, L., 2021. The language of SQL. Addison-Wesley Professional.

Websites

• geeksforgeeks.org, 2024, SQL Tutorial. Available at: https://www.geeksforgeeks.org/sql-tutorial/ [Accessed on: 11.09.2024]
• tutorialspoint.com, 2024, SQL Tutorial. Available at: https://www.tutorialspoint.com/sql/index.htm [Accessed on: 11.09.2024]
• w3schools.com, 2024, SQL Tutorial. Available at: https://www.w3schools.com/sql/default.asp [Accessed on: 11.09.2024]

Author Bio

Sophie Cooper   10 years | MEng

I have completed my MEng in Computer Science from the University of Bristol. I am a programmer and found of educating others and that is why I have been working as an academic writer for the last 10 years. I can write every paper with the highest quality. You can seek support in your Computer Science paper from me.