Database Design and Implementation for ABC Company - HND Project

This project presents a comprehensive database design for ABC Company, a multinational technology corporation. It covers essential aspects of database development, including entity identification (Customer, Supplier, Product, Stock, Order, Payment), normalization to the third normal form (3NF) to eliminate data redundancy, and the creation of a data dictionary detailing table structures and constraints. The project also includes a use case diagram, an entity-relationship diagram illustrating relationships between entities, SQL queries for data retrieval, and implementation details such as table creation scripts and data insertion. The design incorporates primary and foreign key constraints to maintain data integrity. Assumptions are outlined, and the conclusion emphasizes the importance of database design knowledge in various data-intensive services. Desklib provides access to similar projects and resources for students.

Database design for ABC Company

Introduction
ABC Inc. is an American multinational technology company headquartered in Cupertino, California, that
designs, develops, and sells consumer electronics, computer software, and online services. Its hardware
products include the iPhone smartphone, the iPad tablet computer, the Mac personal computer, the
iPod portable media player, the ABC Watch smartwatch, and the ABC TV digital media player. ABC's
consumer software includes the macOS and iOS operating systems, the iTunes media player, the Safari
web browser, and the iLife and iWork creativity and productivity suites. Its online services include the
iTunes Store, the iOS App Store and Mac App Store, ABC Music, and iCloud.
Business rules approaches in software development are concentrated in finding ways and facilities that
would support automatic propagation of business changes from business environment to software
systems. This would help to bridge the gap between business and technology, as aligning information
systems and business operation is one of the fundamental problems in all organizations. To reach that
goal, it has to be clear how business rules should be dealt with, as a special kind of software
requirements, in each phase of the software development lifecycle. Accordingly, at least the following
aspects have to be considered in this process: rules identification, business rules specification, rules
implementation and rules management. A business rules implementation strategy assumes identifying
both the place and the way of how to implement a business rule.
Company business rules:
Term — The focus on business, in that there's no reference to information systems or concepts of
information systems - focuses exclusively on how business people think.
Fact — People involved in the business (as "customers") communicate vocabulary and business rules to
computer professionals (as "suppliers"), in the form of system requirements specifications.
Derivation — Database design should be influenced on the meaning that business people and business
analysts give to certain objects or terms from the business vocabulary. And discussions regarding these
aspects might be endless. If we consider a very simple ecommerce application
Constraint — Constraint is a generic term related to table columns which limits data values in order to
preserve database integrity. Integrity of the relational model restrictions are structural and behavioral

Database Design
Entity Name:
Customer, Supplier, Product, Stock, Order, Payment
Normalization:
Database Normalization is a technique of organizing the data in the database. Normalization is a
systematic approach of decomposing tables to eliminate data redundancy and undesirable
characteristics like Insertion, Update and Deletion Anomalies. It is a multi-step process that puts data
into tabular form by removing duplicated data from the relation tables.
Normalization is used for mainly two purposes,
 Eliminating redundant (useless) data.
 Ensuring data dependencies make sense i.e data is logically stored.
Normalization rule are divided into following normal form.
 First Normal Form
 Second Normal Form
 Third Normal Form
 BCNF
First normal form (1NF)
As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It
should hold only atomic values.
ABCdata (CustLastName, CustFirstName, CustAddress,CustState, CustZip, Custemail, CustPhone,
SuppLastName, SuppFirstName, SuppAddress, SuppState, SuppZip, Suppemail, SuppPhone, ProdName,
UnitPrice, ExpireDate, ProdType, Description, StockDate, Description, ProdID, Quantity, ReorderLevel,
OrderDate, Description, ProdID, Quantity, TotalPrice, Description, Paymentdate, Amount, Due,
Paymenttype, Note)

Second normal form (2NF)
A table is said to be in 2NF if both the following conditions hold:
 Table is in 1NF (First normal form)
 No non-prime attribute is dependent on the proper subset of any candidate key of table.
Customer (CustID, CustLastName, CustFirstName, CustAddress,CustState, CustZip,Custemail,CustPhone,
SuppLastName, SuppFirstName, SuppAddress, SuppState, SuppZip, Suppemail, SuppPhone)
Product (ProdID, ProdName, UnitPrice, ExpireDate, ProdType, Description, StockDate, Description,
ProdID, Quantity, ReorderLevel)
Order (OrderID, OrderDate, Description, ProdID, Quantity, TotalPrice, Description, Paymentdate,
Amount, Due, Paymenttype, Note)
Third Normal form (3NF)
A table design is said to be in 3NF if both the following conditions hold:
 Table must be in 2NF
 Transitive functional dependency of non-prime attribute on any super key should be removed.
 An attribute that is not part of any candidate key is known as non-prime attribute.
In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional
dependency X-> Y at least one of the following conditions hold:
 X is a super key of table
 Y is a prime attribute of table
Customer (CustID, CustLastName, CustFirstName, CustAddress,CustState, CustZip,Custemail,CustPhone)
Employee (EmpID, EmpLastName, EmpFirstName, EmpAddress, EmpState, EmpZip, Empemail,
EmpPhone)
Supplier (SuppID, SuppLastName, SuppFirstName, SuppAddress, SuppState, SuppZip, Suppemail,
SuppPhone)
Product (ProdID, ProdName, UnitPrice, ExpireDate, ProdType, Description, Stock, SuppID)
Stock (StockID, StockDate, Description, ProdID, Quantity, ReorderLevel)
Order (OrderID, OrderDate, Description, ProdID, Quantity, TotalPrice, CustID)
Payment (PaymentID, Description, Paymentdate, Amount, Due, Paymenttype, Note)

Use Case Diagram

Data dictionary
Table Name : Customer
Field Name Data Type Size Primary Key Or
Foreign key
Comments
CustID Number 10 Primary Key
CustLastName Varchar2 100
CustFirstName Varchar2 100
CustAddress Varchar2 150
CustState Varchar2 30
CustZip Varchar2 30
Custemail Varchar2 30
CustPhone Varchar2 30
Table Name : Customer
Field Name Data Type Size Primary Key Or
Foreign key
Comments
EmpID Number 10 Primary Key
EmpLastName Varchar2 100
EmpFirstName Varchar2 100
EmpAddress Varchar2 150
EmpState Varchar2 30
EmpZip Varchar2 30
Empemail Varchar2 30
EmpPhone Varchar2 30
Table Name : Product
Field Name Data Type Size Primary Key Or
Foreign key
Comments
ProdID Number 10 Primary Key
ProdName Varchar2 100
UnitPrice Number 12,2
ExpireDate Date
ProdType Varchar2 2
Description Varchar2 150
Stock Number 10
SuppID Number 10 Foreign key

Table Name : Supplier
Field Name Data Type Size Primary Key Or
Foreign key
Comments
SuppID Number 10 Primary Key
SuppLastName Varchar2 100
SuppFirstName Varchar2 100
SuppAddress Varchar2 150
SuppState Varchar2 30
SuppZip Varchar2 30
Suppemail Varchar2 30
SuppPhone Varchar2 30
Table Name : Order
Field Name Data Type Size Primary Key Or
Foreign key
Comments
OrderID Number 10 Primary Key
OrderDate Date
Description Varchar2 150
ProdID Number 10 Foreign key
Quantity Number 10
TotalPrice Number 12,2
CustID Number 10 Foreign key
Table Name : Stock
Field Name Data Type Size Primary Key Or
Foreign key
Comments
StockID Number 10 Primary Key
StockDate Date
Description Varchar2 150
ProdID Number 10 Foreign key
Quantity Number 10
ReorderLevel Number 10
Table Name : Payment
Field Name Data Type Size Primary Key Or
Foreign key
Comments
PaymentID Number 10 Primary Key
Description Varchar2 150
Paymentdate Date
Amount Number 10
Due Number 10
Paymentatype Varchar2 30
Note Varchar2 200
OrderID Number 10
Entity Relationship Diagram

Implementation

Table creation

Insert Data