Introduction to Databases (COM4008)

Logical Model Design

The first step towards developing a logical model designing for the coffee shop database means the identification of key entities, their attributes, and relationships responsible for the accurate representation of real-world view. The most apparent entities for the coffee shop database include Customer, Order, Coffee, Menu_Item and Order_Details. All of them has its purpose of handling and monitoring several customer interactional, order, and menu data.

Entity Identification

The Customer Entity holds customer details for all customers who patronize the company’s business;/details include Customer_ID, first name, last name, contact details, and email address. The relationship between this entity and the Order entity is the one to many since a single customer may always place several orders. The Order entity contains information about each customer’s order, such as the Order_ID, the date that the order was made, and the foreign key of the Customer_ID.

In the Menu_Item entity, it holds all items which are on the order list with attributes of items such as Item_ID, Item _Name, Category, Price and etc. For meal items that consist of purely coffees, the Coffee entity emerges to to contain the other attributes such as Coffee_ID, Coffee_Name, Coffee_Type and price in relation to cup size inclusive of small, medium and large quantities. Menu_Item is optional to Coffee, meaning that the system is capable of accommodating quite a number of items without limiting them only to coffee.

In contextual model, the Order_Details table is an intermediary table that connects Orders and Menu_Items since the database should be capable of storing the multiple items of a single order. It has fields like Order_Detail_ID, Order_ID, Item_ID, Quantity, and Item_Price to enhance the filed of details of the ordered items.

Attribute Specification

Each entity has been designed with proper attributes to ensure proper operation of the coffee shop. In the table Customer, a key was introduced called Customer ID, which uniquely identifies each customer record. Similarly, Order ID, Item ID, and Order Detail ID are the keys of their respective tables. For linking entities, foreign keys have been defined, such as Customer ID in the Order table and Order ID and Item ID in the Order Details table.

 

Figure 1: Logical Design of Database 

Normalization Process

The database had been normalized up to 3NF in order to rid of redundancy and data integrity. With respect to 1NF, all the attributes carried atomic values with unique entry for every entity. In 2NF, partial dependency was removed by ensuring that the non-key attributes depend only on the full primary key. For instance, in the Order_Details entity, attributes like Quantity and Item_Price depend solely upon composite keys of (Order_ID, Item_ID).

Transitive dependencies were eliminated In Third Normal Form. It ensures that the Price attribute in the Menu_Item table is dependent on only the primary key Item_ID and not on any non-key attribute. Such a structure supports consistency in the data and extensibility in the future without requiring changes to the central schema of the system

As a result, the logical model presented is depicted using crow’s foot notation to enhance on depiction of relationships, cardinalities and keys.

Physical Model Design

The data physical design requires mapping the logical model to a collection of SQL based tables together with data type, key and constraints. Every noun in the logical model is mapped to a table that has clearly defined columns to make the structure flexible to any changes, and to meet database integrity standards.

Figure 2: Physical Design of Database

Table Structures and Data Types

The Customer table is defined by the fields: Customer_ID (INT, AUTO_INCREMENT), First_Name (VARCHAR(50)), Last_Name (VARCHAR(50)), Phone_Number (VARCHAR(15), and Email (VARCHAR(100), UNIQUE). These data types make it possible to provide correct representation of each attribute and avoid duplication when using special fields such as Email. Likewise, the Order table is composed of Order_ID as its foreign key, Order_Date as date data type and Customer ID column as foreign key to the Customer table.

The Menu_Item table consists of the following attributes: Item_ID (INT, AUTO_INCREMENT), the name of the item – Item_Name (VARCHAR(50)), the category – Category (VARCHAR(20), the price – Price (DECIMAL(5,2)), and the optional foreign key – Coffee_ID (INT) – used as a relation between menu items and certain types of coffee. The Coffee table includes the following fields: Coffee_ID INT and an AUTO_INCREMENT feature serve as the primary key for the table, Coffee Name is text field VARCHAR(50), Coffee Type – text VARCHAR(30), and price variations – DECIMAL(5,2) to provide a high level of accuracy: Price_Small, Price_Medium, and Price Large.

Primary Keys, Foreign Keys, and Constraints

The CREATE TABLE statements in the script are easy to understand as follows (by highlighting only ONE unique record key): Customer_ID, Order_ID, Item_ID, Order_Detail_ID, and Coffee_ID are the primary keys to uniquely access each record, as are the foreign keys to relate tables. For example, Customer_ID in the Order table and Order_ID in the Order_Details table helps in implementing referential integrity which means only proper Customer_ID in the Order table and proper Order_ID in the Order_Details table possible as every order should be connected with some valid Customer and every thing in Order_Details table is connected to some valid order. Elements that are used are NOT NULL, UNIQUE and CHECK constraints if they are needed to maintain data’s validity.

Scalability Considerations

To address the scalability feature, the physical model puts into consideration the possibility of extending the number of menus and attributes which makes it easy to add new items without affecting the schema greatly. The fact that the Menu_Item table has an optional foreign key Coffee_ID means that with change in business growth the table is capable of accommodating other foods.

Constructing this structure will give the coffee shop a stable, fast and adaptable database foundation, hence sustainability.

Task 2: Data Analysis and Insights

Introduction

Organizing of library resources involves developing a relational database for the library to help manage them. A well-constructed database helps in the storage, search and update of all related information on books, members of the group and the transactions incurred. Thus, the library can apply a relational database in its work process to improve general performance, keep track of the members’ activities, and enforce members to return books on time. This task involves theulator development of the database schema together with inserting the required records; this aspect of the library system is explained with the help of SQL commands and outcomes in this task.

Database Creation

a. Database Structure

This library's relational database creation requires defining the structure of the database, establishing relationships among different entities, and populating the database with initial data. So, this way, this library will be effective in the management of its resources and also providing adequate access to information by staff and members alike.

Definition of the Structure of the Library Database:

The first step for the design of the library database is to define its structure. The key entities of the library management system are as follows:

1. Books: The table contains details about each book in the library, along with attributes such as Book_ID, Book_Title, Author, Genre, Publisher_ID, Published_Year, and Number_Of_Copies.
2. Members: It holds information about all library members. Key attributes in the Members table include Member_ID, First_Name, Last_Name, Mobile_Number, and Membership_Date.
3. Transactions: This table will keep track of the borrowing/return history of the books. Key fields are Transaction_ID, Member_ID, Book_ID, Issue_Date, Expected_Return_Date, and Actual_Return_Date.
4. Publisher: This table keeps information regarding the publishers of the books. These include Publisher_ID, Publisher_Name, and Publisher_Location.

The above relationships amongst these tables are defined through foreign keys. The Transactions table, for example would reference the Members and Books tables through their IDs to refer to them.

Creating the Database the SQL statement to create the database and its tables is shown here:

1. Creating Database:

   

   Figure 3: Database creation

2. Create Table Address:

   

   Figure 4: creating table query

3. Create Table Member:

   

   Figure 5: Create Table Member

4. Create Table Publisher:

   

   Figure 6: Create table publisher query

5. Create Table Book:

   

   Figure 7: Creating table book

6. Create Table Author:

   

   Figure 8: Creating table author

7. Create Table Transaction:

    

   Figure 9: Table Transaction query

8. Create Table Book_Authors

   

   Figure 10: Creating table book authors(a)

    

   Figure 11: Creating table book authors (b)

b. Data Insertion

After the tables have been created, input records must be added to the database to populate it. These preliminary records would be invaluable for testing and usage in the operations applied in practice. The following are some SQL commands for inputting records into the Publisher, Books, Members, and Transactions tables:

1. Insert Records into the Address Table

   

   Figure 12: Inserting records

2. Insert Records into the Member Table

   

   Figure 13: Insert Records into the Member Table

3. Insert Records into the Publisher Table

   

   Figure 14: Inserting records into Publisher Table

4. Insert Records into the Book Table

   

   Figure 15: Inserting Records into the Book Table

5. Insert Records into the Author Table

   

   Figure 16: Insert Records into the Author Table

6. Insert Records into the Book_Authors Table

   

   Figure 17: Insert Records into the Book_Authors Table

7. Insert Records into the Transaction Table

   

   Figure 18: Insert Records into the Transaction Table

c. Displaying Tables

Output Verification After executing SQL commands that create the tables and populate them with records, the output should be verified. This could have been done through a succession of executing several SELECT commands to verify if data exists in each of the created tables. Successful execution of the commands is an indication that the database has been set up correctly and is ready for use.

1. Data of Address Table

   

   Figure 19: Data of Address Table

2. Data of Member Table

   

   Figure 20: Data of Member Table

3. Data of Publisher Table



Figure 21: Data of Publisher Table

4. Data of Book Table

   

   Figure 22: Data of Book Table

5. Data of Author Table

   

   Figure 23: Data of Author Table

6. Data of Transaction Table

   

   Figure 24: Data of Transaction Table

Task 3: Test the Library Relational Database

Introduction

This task is the testing of the relational library database developed during Task 2. The database will be validated for accuracy and integrity of its data. Four particular problem cases will be examined, notably: retrieve a list of details associated with transactions by their issue date; search for a list of members by contact number; retrieve a list of details about a particular book by title. We will also manipulate data by deleting a damaged book and updating a member's contact information. Finally, we shall implement an appreciation system that shall calculate total appreciation points for each member. Each case will be executed with the execution of SQL queries and their results should be analysed to confirm that the system is operational as desired.

Case 1: Search

1. Transaction Details by Issue Date

   

   Figure 25: Transaction Details by Issue Date

2. Member Search by Contact Number

   Search for the member whose mobile number is 447123987654.

   

   Figure 26: Member Search by Contact Number

3. Book Details by Title

   Retrieve details for the book titled 'Pride and Prejudice

   

   Figure 27: Book Details by Title

4. Explanation

   The first question will return information on each transaction based upon a particular date of issue. It will pick out for every transaction the transaction ID, the member's last name, the title of book, the issue date, the expected date of return, and the date of actual return. From the result, members Johnson and Jackson lent books on that day, thereby disclosing the borrowing activity of that particular day.

   The second question looks for a member by contact number. This is done by joining the Member and Transaction tables on Member_ID. It produces relevant transaction information based on members having a specified contact number, thus making transaction data related to members accessible.

   The third question is to get information about books based on titles. Again, this is a join operation between the Transaction table and the Book table to match the titles of books with transactions that they correspond to. The results show that the database was in a position to map titles to their corresponding books, as well as their transactions. This means it can solve questions related to queries in member transaction queries and data about their books.

   Together, the SQLs shown above display the adequacy and effectiveness of this relational database of the library in handling and retrieving necessary information within specific search criteria.

Case 2: Data Manipulation

1. Delete a Damaged Book

   Delete the book titled Moby Dick from the Book table.

   

   Figure 28: Delete a Damaged Book

   In order to keep the quality of the books at the library, any book must be removed from the database that has damage. An SQL DELETE statement deletes a specific book from the database. For example, the query DELETE FROM Book WHERE Book_Title = 'Moby Dick'; deletes the damaged book from the library's database so that the same broken book cannot be checked out from the library again. This is just one method of ensuring that no broken items or damage can be inflicted on the collection for the library.

2. Update Member Contact Number

   Update the contact number of the member with Member_ID = 39 to a new value 447876543210.

   

   Figure 29: Update Member Contact Number

   Updating a member's contact number is crucial to maintain good liaison between the members of the system within the library. We can modify existing records in the Member table using the SQL UPDATE command. For example, the effect of executing UPDATE MEMBER SET Mobile_Number = '447876543210' WHERE Member_ID = 39; will be that the member's contact details are updated so that the library can contact the member regarding overbooks, events, or any policy change in the library.

Case 3: Appreciation System

1. Adding Comments and Appreciation Points









Figure 30: Adding Comments and Appreciation Points

The library added new columns to the Transaction table: Comments and Appreciation_Points. These columns enable staff to document their comments on the returned books with regard to their condition—this could be excellent, good, or poor. It induces borrowing members to return books in good condition, having appreciation points tied to this. Everyone in the library, by default, derives benefits from this set of additions.

2. Calculating Total Appreciation Points

The appreciation points of each member are calculated through all the points reflected in the Transaction table. Executing the SQL query SELECT Member_ID, SUM(Appreciation_Points) AS Total_Points FROM Transaction GROUP BY Member_ID; enables the library to determine each member's overall appreciation score. It may make use of this data for the recognition or incentives it will award to members and promote accountability and responsible borrowing.



Figure 31: Calculating Total Appreciation Points (a)



Figure 32: Calculating Total Appreciation Points (b)

Case 4: Statistics

1. Total Members Count



Figure 33: Total Members Count

When it comes to measuring library usage one must tally out the number of unique library card holders who had used the services of the library within the month. This information can be obtained from the SQL query SELECT COUNT(DISTINCT Member_ID) AS Total_Members_Visited FROM Transaction WHERE MONTH(Issue_Date) = 10; Member participation data enables the library to make rational decisions about how to allocate resources, services and programming to members with a view of increasing their participation.

References

• Alamsyah, A, Widiyanesti, S, Wulansari, P & Rahadi, A 2023, 'Blockchain traceability model in the coffee industry', Journal of Open Innovation: Technology, Market, and Complexity, Elsevier, viewed 9 October 2024, https://www.sciencedirect.com/science/article/pii/S2199853123001105.
• Berampu, LT, Sirojuzilam, S, Sembiring, B, Syahputra, H & Rahmat, F 2019, 'The relationship of consumer preference, value creation, and global supply chain in the third wave coffee business in Sumatera Utara, Indonesia', International Journal of Research, ResearchGate, viewed 9 October 2024, https://www.researchgate.net/publication/346987564_The_Relationship_of_Consumer_Preference_Value_Creation_and_Global_Supply_Chain_in_the_Third_Wave_Coffee_Business_in_Sumatera_Utara_Indonesia.
• Cintya, C & Siahaan, RF 2020, 'The client-server system for ordering food and beverages using the waterfall method', Journal of Intelligent Decision Support Systems, IOCS Publisher, viewed 9 October 2024, http://idss.iocspublisher.org/index.php/jidss/article/view/13.
• Ferreira, J, Ferreira, C & Bos, E 2021, 'Spaces of consumption, connection, and community: Exploring the role of the coffee shop in urban lives', Geoforum, Elsevier, viewed 9 October 2024, https://www.sciencedirect.com/science/article/pii/S0016718520303080.
• Ibrahim, B, Aljarah, A & Sawaftah, D 2021, 'Linking social media marketing activities to revisit intention through brand trust and brand loyalty on the coffee shop Facebook pages', Sustainability, MDPI, viewed 9 October 2024, https://www.mdpi.com/2071-1050/13/4/2277.
• Kim, T & Yun, S 2019, 'How will changes toward pro-environmental behavior play in customers' perceived value of environmental concerns at coffee shops?', Sustainability, MDPI, viewed 9 October 2024, https://www.mdpi.com/2071-1050/11/14/3816.
• Lee, CH, Chen, CH, Li, F & Shie, AJ 2020, 'Customized and knowledge-centric service design model integrating case-based reasoning and TRIZ', Expert Systems with Applications, Elsevier, viewed 9 October 2024, https://www.sciencedirect.com/science/article/pii/S0957417419307791.
• Li, X, Zhao, X, Wang, Y, Liu, Y, Chen, C & Long, C 2024, 'OpenSiteRec: An Open Dataset for Site Recommendation', Proceedings of the 47th International ACM SIGIR Conference on Research and Development in Information Retrieval, ACM, viewed 9 October 2024, https://dl.acm.org/doi/abs/10.1145/3626772.3657875.
• Li, Y, Feng, AX, Li, J, Mumick, S, Halevy, A & Li, V 2019, 'Subjective databases', arXiv Preprint, arXiv, viewed 9 October 2024, https://arxiv.org/abs/1902.09661.
• Pradana, IGMT & Djatna, T 2020, 'Blockchain modeling for traceability information system in supply chain of coffee agroindustry', IEEE Conference on Advanced Informatics, Concepts, Theory, and Applications, IEEE, viewed 9 October 2024, https://ieeexplore.ieee.org/abstract/document/9263214/.
• Servín-Juárez, R, Trejo-Pech, CJO & Pérez-Vásquez, AY 2021, 'Specialty coffee shops in Mexico: Factors influencing the likelihood of purchasing high-quality coffee', Sustainability, MDPI, viewed 9 October 2024, https://www.mdpi.com/2071-1050/13/7/3804.
• Spence, C & Carvalho, FM 2020, 'The coffee drinking experience: Product extrinsic (atmospheric) influences on taste and choice', Food Quality and Preference, Elsevier, viewed 9 October 2024, https://ora.ox.ac.uk/objects/uuid:c3720965-ca56-490f-9c82-90a63de4d54d/files/rc821gj798.

UPTO56%

Avail The Benefit Today!

To View this & another 50000+ free

Enter Email

Submit