MBIS4002 Database Management Systems Report Sample

MBIS4002 Database Management Systems

Assessment Description

In this project, you will work in a group to write a report, design, implement, loading and testing a relational database, by using free Online App, www.diagram.net, thePowerful Database Management System, PostgreSQL, and the command line client SQL Shell (psql) to complete tasks.

Project Specification:

Part A: Entity Relationship Diagram

1. Your first task is to study the Case study requirements and gather as much knowledge to list all the main Entities and attributes.

2. It is required students to use Free Online App/Tool,
https://www.diagrams.net/ and Crow’s Foot Notation to Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.

Your entity relation diagram that models your database design should:

i. Include all entities, relationships (including names) and attributes.

ii. Identify primary and foreign keys.

iii. Include cardinality/ multiplicity and show using crow’s feet notation.

iv. Include participation (optional / mandatory) symbols if applicable.

Note: The ERD should be created as an editable file .peg or .svg. Hand-drawn diagrams, photos or screenshots from mobile phone will not be accepted.

Part B: Normalization

1. Normalize all entities and relationships in optimal normal form. Provide the normalized schema and discuss the level of normalization achieved for each relation. You goal should be normalise all entities in 3NF. Provide the reasons for any relation that is not maintained in 3NF.

Part C: Relational schema and Implementation of the PostgreSQL database

1. Create relational schema that matches ERD and normalized schemas you created in Part A and B. The schema should include suitable data validation, integrity and referential integrity constraints, for example, types and length of attributes, primary and foreign keys etc. Make sure you follow standards, naming conventions and notation.

2. Implementation of the relational database for loading into the PostgreSQL database server includes creating the script file AMCDB.sql, showing how to create tables and any constrains to build relationships between tables, insert values into tables, e.g. use boygirl.sql file as an example to create tables, to link tables using constrains of Foreign Key, and to insert values into each table. In addition, your group should demonstrate that the implemented relational database script file AMCDB.sql can be successfully loaded into the PostgreSQL database server through the command line client, psql.

(1) Create tables according to your relational schema created in step 1.

a. you are required to develop a demonstration prototype system (database that stores data in the PostgreSQL database server for AMC stores, employees, products, suppliers, patrons and orders. Create a new database called AMCDB.sql.

b. Follow a standard naming convention for table names and also field names. Avoid using spaces and any special characters in table and field names. Use the camelCase or underscore_case to separate parts of a name.

c. You are required to be consistent in tables’ name, fields’ name, data types, field size and key constraints with relational schema created in step 2.

d. Use DESCRIBE command to describe table the structure of each table in your AMCDB database, e.g. \d employee

(2) Insert values into tables, add at least five records into each table.

Part D: SQL

It is required that students must complete Part D, by using the client, SQL Shell (psql) to load the implemented database script file, AMCDB.sql into the PostgreSQL database server for storing the relational database, and testing these designed questions in Part D.

1. List the full name of Patrons, phone number and address in the descending order of customer number. The full name is comprised of first and last name joined with single space. Use the alias Customer full name for the composed column heading.

2. List all the products having price less than $100.

3. List the product number, product title, quantity ordered, unit price and total amount per product (unit price * quantity ordered) for order having order number 1005.

4. List name, full address and fax number of stores that has the word ‘George’ anywhere in the street component of address. Your query should consider the case sensitivity of the street as well i.e. the stores with words ‘George’, ‘GEORGE’, ‘george’, ‘GeorGe’ etc. in street should be returned by your query too. (0.5 marks)

5. List all the orders having at least two different products ordered.

6. List customer number, name and address of all the customers who has not placed any order.

7. Retrieve first name, last name and employee number of all the employees along with department name and store name where they work.

8. For each store, display store name and number of employees works in account department.

9. List all the orders placed for financial year 2018 i.e. orders placed after 30/06/2017 and before 01/07/2018.

10. Retrieve customer number and the total number of orders placed by each customer.

11. For each order, retrieve the order number, order date and number of products ordered in it. Sort the output in descending order of order number.

Solution

Background

Requirements

A complete database management system for the Australian Manufacturing Company (AMC) that takes into account various operational elements must be created as part of the project. This covers the effective management of businesses, personnel, goods, vendors, customers, and orders. The database system aims to assist better corporate decisions and procedures. The Entity Relationship Diagram (ERD) for the project has been created using Crow's Foot Notation, and all entities must be normalized to at least the Third Normal Form (3NF). The schema included data validation and referential integrity restrictions as well as following naming standards. In PostgreSQL, implementation entails building tables, setting up relationships, and adding data (Zhong et al. 2020). The professionals have built SQL queries to help with a variety of tasks, including data retrieval and analysis.

Mission Statement

In order to create a solid conceptual data model and a dynamic physical database, the Australian Manufacturing Company (AMC) has hired professionals to function as database system consultants. AMC's operational environment is about to undergo a transformation, and this effort attempts to set the framework for it. AMC has aimed to design a scalable and reliable database solution with the help of our database expertise that perfectly fits AMC's needs and improves the management of stores, staff, products, suppliers, customers, and orders (Akhtar, 2023). In order to empower AMC with efficient operations, datadriven decisionmaking, and the potential for future technological breakthroughs, professionals must build an agile data management system for MBA assignment expert.

DBMS Selection

"PostgreSQL has been selected as the best option for the planned database management system (DBMS) for the Australian Manufacturing Company (AMC). AMC's needs are well met by the robust and adaptable open source platform provided by PostgreSQL (Christiono, 2020). It is the perfect option for managing numerous data entities including stores, workers, items, suppliers, customers, and orders because of its relational database capabilities, support for complex data types, and adherence to SQL standards (Helinda et al. 2020). The professionals want to provide AMC with a scalable and trustworthy database management system (DBMS) by choosing PostgreSQL that not only satisfies the organization's present demands but also paves the way for a futuristic future 

Part A: ERD

A database system's logical structure and relationship between entities are depicted graphically in an entity relationship diagram (ERD). Database designers and other stakeholders can use it as a visual tool to see how various bits of information are connected to one another (Vershinin and Mustafina, 2021). The ERD exposes the key entities, their characteristics, and their relationships within the context of the AMC sales database.

Store Entity: This entity collects data about specific stores. 'store_id', 'name', 'phone_number', and 'email_address' attributes offer information about each store. The main key that specifically identifies each store is'store_id'.

Department entity. 'Department_id', 'title', 'phone_number', and 'email_address' are properties that describe departments. The 'department_id' primary key creates uniqueness.

Entity for Payslips: Stores data about employees' payslips. The primary key is 'payslip_id', and properties like 'payslip_number', 'hours_worked', 'gross_pay', and 'payslip_date' provide pertinent information. This object is linked to "Employee" and "Store" through foreign keys.

Employee Entity: The characteristics 'employee_id', 'first_name', 'last_name', and 'email' are among those found in employee data. The entity connects to the 'Department' entity through the foreign key 'department_id' and to the 'Store' entity via the foreign key'store_id'.

ProductSupplier Entity: Uses the foreign keys "product_id" and "supplier_id" to facilitate the link between products and suppliers. It makes it possible to represent many suppliers for the same product and vice versa.

Figure 1: Entity relationship diagram
(Source: Self-created)

Product entities. 'Product_id', 'Name', 'Brand', 'Description', and 'Price' are some of the properties that the object has. Primary key is 'product_id'.

Supplier entity. 'supplier_id', 'name', 'contact_name', 'contact_email', and 'contact_phone' are examples of attributes. 'supplier_id' is the main key.

Patron entity. The main key is "patron_id," and the attributes are "first_name," "last_name," "mobile phone number," and "address."

Inventory entity. It has foreign key connections to the "Store" and "Product" entities and has characteristics like "quantity_available" and "quantity_ordered."

OrderProduct Entity: Establishing a many-to-many link between "Order" and "Product" entities is the responsibility of the OrderProduct Entity. It employs composite primary keys that relate to "order_id" and "product_id," and it also contains "quantity_ordered."

Entity for Orders: This entity records information on orders, including order_id, order_number, and order_date. Orders are linked by foreign keys to certain customers and retailers.

The ERD depicts various relationships:

OnetoMany: Department to Store, Employee to Store, Payslip to Employee, Employee to Order, and Patron to Order.

ManytoOne: Employee to Store, Employee to Department, Payslip to Employee, Order to Patron, Order to Store,

Many-to-many: Product to Supplier, Order to Product. 

Part B: Normalized Schema

1st Normal Form (1NF)

Table 1: Store
(Source: Self created)

2nd Normal Form (2NF)

Table 2: Store
(Source: Selfcreated)

Table 3: Department
(Source: Self created)

3rd Normal Form (3NF)

Table 4: Store
(Source: Self created)

Table 5: Department
(Source: Self created)

Table 6: Employee
(Source: Selfcreated)

Each entity in the AMC sales project has been methodically constructed in line with the 1st Normal Form (1NF) principles, assuring data integrity and reducing redundancy. Atomicity is supported by the treatment of attributes within each entity as unique and indivisible values (Wiseso et al. 2020). The "Store" entity maintains its characteristics such as "name," "phone_number," and "email_address" as individual non-composite values. The main keys "store_id" for "Store," "department_id" for "Department," and "employee_id" for "Employee" uniquely identify each entity entry, simplifying accurate data retrieval and manipulation. This method also applies to entities that are connected to one another, such as "ProductSupplier" and "Inventory," where relationships are created via foreign keys without going against atomicity (Martins et al. 2021). The database architecture creates a strong basis for additional normalization by following the 1NF principles, increasing data organization and minimizing anomalies in the AMC sales data management system.

Part C: Database Schema

Create Table Queries

Figure 2: Store Table

A "Store" table is created in a database for the AMC sales management system using the supplied SQL query. The database has a variety of characteristics, each specifying a different characteristic of a shop, including "store_id," "name," "phone_number," "email_address," "fax_number," "street_number," "suburb," "state," and "postcode." The primary key for uniquely identifying each store is set to "store_id". Compliant design, atomic values and little to no data redundancy are guaranteed because of the table's 1st Normal Form (Viloria et al. 2019). The foundation for an orderly and effective data storage solution for the AMC sales activities is laid forth by this structured representation.

Figure 3: Department Table

The query generates a "Department" table with the following attributes "department_id," "title," "phone_number," and "email_address." The main key is "department_id". Department information will be kept in this table. This architecture guarantees data organization and retrievability, enabling effective system administration of department-related data.

Figure 4: Employee Table

The "Employee" table, which holds employee information, is defined by the query. It contains information such the "employee_id," "first_name," "last_name," "address," "mobile," "email," "tfn" (tax file number), "salary," "employment_type," "annual," "hourly," and "joining_date" characteristics (Valentin and Marilena-Catalina, 2020). The main key is the "employee_id". Foreign key references connect workers to the appropriate stores and departments.

Figure 5: Payslip Table

The table establishes connections with both employees and stores using foreign keys. This system enables the tracking of employee earnings, working hours, and other relevant data for efficient payroll administration. It contains information such as "payslip_id," "payslip_number," "hours_worked," "employee_id," "store_id," "gross_pay," and "payslip_date."

Figure 6: Product Table

Figure 7: Supplier Table

Figure 8: ProductSupplier Table

Figure 9: Inventory Table

Figure 10: Patron Table

Figure 11: CustomerOrder Table

The "order_number," "order_date," and references to customers and retailers via foreign keys are all managed by this table. As the main key, "order_id" ensures separate order records. Effective order monitoring and client engagement are made possible by this arrangement.

Figure 12: OrderProduct Table

The relationship between orders and goods is shown in the "OrderProduct" table. It uses the "order_id" and "product_id" from the corresponding tables as references. Each order-product pair is guaranteed to be unique by the main key, which is made up of the two foreign keys (Martinho et al. 2020). This layout makes it possible to track purchased items correctly inside orders.
Insert Table Quries

Figure 13: Insert Value queries for Store table

Adds information about various stores, such as names, contact details, and locations, to the "Store" table. The "store_id," which functions as the main key, acts as a distinctive identifier for each store. Effective shop management and communication are made possible by this data.

Figure 14: Insert Value queries for Department table

Adds more departments, along with their names and contact details, to the "Department" table. The primary key used to identify each department is "department_id". The departmental organization and communication within the company are supported by this data.

Figure 15: Insert Value queries for Employee table

The "Department" table is expanded to include other departments, along with their names and contact details. In order to identify each department, the "department_id" serves as the main key. The departmental organization and communication are supported by this information.

Figure 16: Insert Value queries for Payslip table

Adds payslip data, including payslip numbers, hours worked, gross pay, and related employee and store data, to the "Payslip" table. A distinct "payslip_id" is assigned to each payslip record as the main key, enabling accurate payslip tracking and computation.

Figure 17: Insert Value queries for Product table

Adds product information, including names, brands, descriptions, and pricing, to the "Product" table. A primary key called "product_id" guarantees different product identification. The management of inventories and sales depends on this information.

Figure 18: Insert Value queries for Supplier table

Adds product information, including names, brands, descriptions, and pricing, to the "Product" table. A primary key called "product_id" guarantees different product identification. The management of inventories and sales depends on this information.

Figure 19: Insert Value queries for Product Supplier table

Creates connections between suppliers and goods in the "ProductSupplier" database. Both the "supplier_id" and "product_id" are foreign keys that relate to the appropriate tables. Effective management of product-supplier associations is made possible by this link.

Figure 20: Insert Value queries for Inventory table

Inserts inventory information into the "Inventory" database, indicating the quantity of goods that are ordered and available in retailers. A composite primary key created from the combined "store_id" and "product_id" enables precise inventory management for each store and product.

Figure 20: Insert Value queries for Patron table

Adds customer information to the "Patron" database, such as names, phone numbers, and addresses. As the main key, a special "patron_id" is given to each patron, enabling the storing and organization of structured patron data.

Figure 21: Insert Value queries for Customer Order table

Adds order details, such as order numbers, order dates, and linkages to customers and retailers, to the "Order" table. As the main key, "order_id" enables efficient order tracking and customer communication.

Figure 22: Insert Value queries for Customer Order table

Adds order details to the "Order" table, such as order numbers, order dates, and linkages to customers and retailers. The "order_id" acts as the main key, enabling efficient order tracking and customer communication.

Select all table Quries in psql

Figure 23: Successful Connection to the Database in Psql

A successful connection to the "AMC_sales" database on localhost was made with the "postgres" user using the PostgreSQL command-line interface.

Figure 24: Select table queries for Store table

All data from the "Store" table in the "AMC_sales" database was retrieved. Store information such as ID, name, phone number, email address, and location are listed in columns.

Figure 25: Select table queries for Department table

The "Department" table in the "AMC_sales" database was queried for all entries. Department ID, title, phone, and email are among the fields. The outcome includes information about numerous corporate divisions.

Figure 26: Select table queries for Employee table

The "Employee" table in the "AMC_sales" database was queried for data. The outcome shows the employee's information, including ID, name, contact information, pay, job status, and related department and retailer.

Figure 27: Select table queries for Payslip table

Taken information out of the "AMC_sales" database's "Payslip" table. Records from payslips, including ID, number, hours worked, employee and shop IDs, gross pay, and payslip date, are shown as a consequence.

Figure 28: Select table queries for Product table

The "Product" table in the "AMC_sales" database was queried for data. The result shows product information, such as ID, name, brand, description, and price, making it easier to manage and comprehend the items that are on offer.

Figure 29: Select table queries for Supplier table

Ran a query on the "AMC_sales" database's "Supplier" table. The output displays supplier data, including ID, name, and contact information, facilitating communication and supplier management.

Figure 30: Select table queries for Supplier table

The "ProductSupplier" table of the "AMC_sales" database was queried. The output shows connections between items and their associated suppliers, which makes inventory management and procurement management easier for the company.

Figure 31: Select table queries for Supplier table

The "Inventory" table in the "AMC_sales" database was queried for data. The records that are shown provide information about the stock levels and quantity orders for various items across multiple retailers, which helps with inventory management and order fulfillment procedures.

Figure 32: Select table queries for Patron table

A "SELECT" query was run on the "Patron" table in the "AMC_sales" database. Customers' first and last names, unique patron IDs, mobile phone numbers, and addresses were all returned by the query, allowing for efficient client administration and communication.

Figure 33: Select table queries for Customer Order table

A "SELECT" query was run on the "CustomerOrder" table in the "AMC_sales" database. In order to facilitate effective order monitoring and administration, the query gathered customer order information from the database, including order IDs, order numbers, order dates, linked patron IDs, and store IDs.

Figure 34: Select table queries for Order Products table

Order IDs, related product IDs, and quantities ordered were retrieved from the "OrderProduct" table enabling easier order-product linkage. 

Foreign Key Constraints

In a relational database, foreign key constraints provide referential integrity between tables. A column or combination of columns in one table (the child table) that refers to the primary key column(s) in another table (the parent table) is said to have a foreign key relationship. This mandates that the main key column(s) of the parent table and the child table's referring column(s) must have identical values (Tarczali et al. 2021). In order to preserve consistency, whenever a referenced value is added, removed, or changed in the parent database, the appropriate changes are cascaded to the child table.

Figure 35: Employee Table with Foreign key Constraints

Figure 36: Payslip Table with Foregin key Constraints

Figure 37: Product Supplier Table with Foregin key Constraints

Figure 38: Inventory Table with Foregin key Constraints

Figure 39: Customer Order Table with Foregin key Constraints

Figure 40: Order Product Table with Foregin key Constraints

Foreign key restrictions safeguard proper relationships and dependable database operations by preventing invalid or inconsistent data.

Part D: SQL Statements

 

 

Figure 43: Query 3 Statement

 

Figure 45: Query 5 Statement

 

 

 

Figure 50: Query 10 Statement

The supplied SQL statements show examples of searches made on the "AMC_sales" database. They obtain information on establishments, divisions, personnel, paystubs, goods, suppliers, stock, clients, and orders for goods placed by customers. In order to link tables and provide useful information, they use joins. Statistics are computed and data are combined using the aggregation functions COUNT and CONCAT. Results can be filtered, grouped, and sorted using conditions, GROUP BY, and ORDER BY clauses (Fredstam, and Johansson, 2019). The queries also demonstrate handling NULL values and particular data searches. These statements include information about customers, orders, goods, staff, and more, assisting in the analysis and management of different corporate database elements. 

Group Report

Challenges & Solutions

Managing complicated interactions like many-to-many and managing possible performance bottlenecks as data increases are among the project's challenges. Other challenges include assuring data correctness and integrity across many organizations. Effective data validation techniques, referential integrity requirements, the use of indexing for query optimization, and the prudent application of denormalization for performance advantages are possible solutions. Data preservation also depends on frequent backups and disaster recovery procedures. The project's ability to successfully manage the many operational components of AMC will depend on its ability to strike a balance between normalization and performance optimization while adhering to industry norms. 

References

Akhtar, A., 2023. Popularity Ranking of Database Management Systems. arXiv preprint arXiv:2301.00847. Retrieved from: https://arxiv.org/abs/2301.00847 Retrieved on 26th Aug 2023

Christiono, K., 2020, November. Studi Komparasi Database Management System Antara Maria Db Dan Postgresql Terhadap Efisiensi Penggunaan Sumber Daya Komputer. In Conference on Business, Social Sciences and Innovation Technology (Vol. 1, No. 1, pp. 573-579). Retrieved from: https://journal.uib.ac.id/index.php/cbssit/article/view/1464 Retrieved on 24th Aug 2023

Fredstam, M. and Johansson, G., 2019. Comparing database management systems with SQLAlchemy: A quantitative study on database management systems. Retrieved from: https://www.diva-portal.org/smash/record.jsf?pid=diva2:1298578 Retrieved on 25th Aug 2023

Helinda, A., Musliyana, Z., TB, D.R.Y., Dwipayana, M., Suanda, J. and Johari, A.N., 2020, November. Performance analysis of heterogeneous database management system (DBMS) synchronization using message digest 5. In AIP Conference Proceedings (Vol. 2291, No. 1). AIP Publishing. Retrieved from: https://pubs.aip.org/aip/acp/article-abstract/2291/1/020090/725106 Retrieved on 25th Aug 2023

Martinho, N., Almeida, J.P.D., Simões, N.E. and Sá-Marques, A., 2020. Urbanwater: Integrating epanet 2 in a postgresql/postgis-based geospatial database management system. ISPRS International Journal of Geo-Information, 9(11), p.613. Retrieved from: https://www.mdpi.com/2220-9964/9/11/613 Retrieved on 25th Aug 2023

Martins, P., Tomé, P., Wanzeller, C., Sá, F. and Abbasi, M., 2021. Comparing Oracle and PostgreSQL, Performance and Optimization. In Trends and Applications in Information Systems and Technologies: Volume 2 9 (pp. 481-490). Springer International Publishing. Retrieved from: Retrieved on 24th Aug 2023https://link.springer.com/chapter/10.1007/978-3-030-72651-5_46 Retrieved on 25th Aug 2023

Tarczali, T., Lehotay-Kéry, P. and Kiss, A., 2021. Membrane clustering using the postgresql database management system. In Intelligent Systems and Applications: Proceedings of the 2020 Intelligent Systems Conference (IntelliSys) Volume 2 (pp. 377-388). Springer International Publishing. Retrieved from: https://link.springer.com/chapter/10.1007/978-3-030-55187-2_30 Retrieved on 24th Aug 2023

Valentin, P. and Marilena-Catalina, D., 2020. Enhanced Distributed Data Mining Application using the PostgreSQL Database Management System. In Proceedings of the 15th International Conference on Virtual Learning (pp. 432-438). Retrieved from: https://pesquisa.bvsalud.org/global-literature-on-novel-coronavirus-2019-ncov/resource/pt/covidwho-1361114 Retrieved on 25th Aug 2023

Vershinin, I.S. and Mustafina, A.R., 2021, September. Performance analysis of PostgreSQL, MySQL, microsoft SQL server systems based on TPC-H tests. In 2021 International Russian Automation Conference (RusAutoCon) (pp. 683-687). IEEE. Retrieved from: https://ieeexplore.ieee.org/abstract/document/9537400/ Retrieved on 24th Aug 2023

Viloria, A., Acuña, G.C., Franco, D.J.A., Hernández-Palma, H., Fuentes, J.P. and Rambal, E.P., 2019. Integration of data mining techniques to PostgreSQL database manager system. Procedia Computer Science, 155, pp.575-580. Retrieved from: https://www.sciencedirect.com/science/article/pii/S1877050919309949 Retrieved on 24th Aug 2023

Wiseso, L.G., Imrona, M. and Alamsyah, A., 2020, October. Performance analysis of Neo4j, MongoDB, and PostgreSQL on 2019 national election big data management database. In 2020 6th International Conference on Science in Information Technology (ICSITech) (pp. 91-96). IEEE. Retrieved from: https://ieeexplore.ieee.org/abstract/document/9392041/ Retrieved on 24th Aug 2023

Zhong, R., Chen, Y., Hu, H., Zhang, H., Lee, W. and Wu, D., 2020, October. Squirrel: Testing database management systems with language validity and coverage feedback.
In Proceedings of the 2020 ACM SIGSAC Conference on Computer and Communications Security (pp. 955-970). Retrieved from: https://dl.acm.org/doi/abs/10.1145/3372297.3417260 Retrieved on 25th Aug 2023