Database Schema Report: Design and Implementation of Database Tables

This report provides a detailed analysis of a database schema, focusing on the design and structure of three key tables: CUSTOMER, ORDER, and INVENTORY. The CUSTOMER table includes attributes such as customerID, firstName, lastName, phone, email, address, suburb, postcode, and loyaltyDiscount. The ORDER table contains orderID, customerID, dateOrdered, dateFulfilled, postageAmount, and discountApplied, with a foreign key relationship to the CUSTOMER table. The INVENTORY table comprises inventoryID, categoryID, title, abstract, yearPublished, and unitPrice, while the ORDER_LINE table links orders and inventory items. The report also references several academic sources related to database design, schema generation, and database preservation. These sources cover topics from automatic R2RML mapping to the creation of a database preservation toolkit and named-entity relationship exploration. This report provides a comprehensive overview of the database schema design and its components.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Student
Name of the University

1DATABASE MANAGEMENT SYSTEM
Relational Schema:
Table: CUSTOMER
Attribute Data Type Primary Key Foreign
customerID int Yes
firstName varchar (40)
lastName varchar (40)
phone int
email varchar (40)
address varchar (40)
suburb varchar (40)
postcode int
loyaltyDiscount double
Table: ORDER
Attribute Data Type Primary Key Foreign
orderID int Yes
customerID int CUSTOMER
(customerID)
dateOrdered DATE
dateFulfilled DATE
postageAmount decimal(10,2)
discountApplied double

2DATABASE MANAGEMENT SYSTEM
Table: INVENTORY
Attribute Data Type Primary Key Foreign
inventoryID int Yes
category Int CATEGORY(categoryID)
title varchar (40)
abstract varchar (500)
yearPublished Year
unitPrice decimal(10,2)
Table: ORDER_LINE
Attribute Data Type Primary Key Foreign
orderID int Yes
inventoryID int Yes INVENTORY
(inventoryID)
dateOrdered DATE
dateFulfilled DATE
postageAmount decimal(10,2)
discountApplied double

3DATABASE MANAGEMENT SYSTEM
Bibliography:
de Medeiros, L. F., Priyatna, F., & Corcho, O. (2015). MIRROR: Automatic R2RML mapping
generation from relational databases. In International Conference on Web Engineering
(pp. 326-343). Springer, Cham.
El‐Assady, M., Sevastjanova, R., Gipp, B., Keim, D., & Collins, C. (2017). NEREx: Named‐
Entity Relationship Exploration in Multi‐Party Conversations. In Computer Graphics
Forum (Vol. 36, No. 3, pp. 213-225).
Ferreira, B., Faria, L., Ramalho, J. C., & Ferreira, M. (2016). Database Preservation Toolkit: A
relational database conversion and normalization tool. In iPRES: 13th International
Conference on Digital Preservation.
Mandery, C., Terlemez, O., Do, M., Vahrenkamp, N., & Asfour, T. (2015). The KIT whole-body
human motion database. In Advanced Robotics (ICAR), 2015 International Conference
on (pp. 329-336). IEEE.
Mathias, S. L., Hines-Kay, J., Yang, J. J., Zahoransky-Kohalmi, G., Bologa, C. G., Ursu, O., &
Oprea, T. I. (2013). The CARLSBAD database: a confederated database of chemical
bioactivities. Database, 2013, bat044.
McMinn, P., Wright, C. J., Kinneer, C., McCurdy, C. J., Camara, M., & Kapfhammer, G. M.
(2016, October). SchemaAnalyst: Search-based test data generation for relational
database schemas. In Software Maintenance and Evolution (ICSME), 2016 IEEE
International Conference on (pp. 586-590). IEEE.

4DATABASE MANAGEMENT SYSTEM
Pham, M. D., Passing, L., Erling, O., & Boncz, P. (2015). Deriving an emergent relational
schema from rdf data. In Proceedings of the 24th International Conference on World
Wide Web (pp. 864-874). International World Wide Web Conferences Steering
Committee.