Database Design for ABC Company

This document provides an overview of the database design for ABC Company, including normalization, entity relationships, and implementation. It covers topics such as customer, supplier, product, stock, order, and payment entities, as well as use case diagrams, data dictionary, and implementation details. The document also includes sample queries, forms, and reports.

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

Constraints:
Primary Key
 CustID
 EmpID
 SuppID
 ProdID
 StockID
 OrderID
 PaymentID
Foreign Key
 Product (SuppID)
 Stock (ProdID)
 Order (ProdID)
 Order (CustID)
Assumptions
 Make sure to include an overview of all the requirements to start your business
 Outline all of your products, your inventory and packaging
 Understanding of the current market and your competition
 Customer service and customer acquisition
 Consider all the development requirements
 Budget planning, break-even analysis, projected profit and loss
 Products and services offered by your business

Forms

Reports

Queries
SELECT Customer.CustLastName, Customer.CustAddress, Customer.CustPhone
FROM Customer;
SELECT Payment.Paymentdate, Avg(Payment.Amount) AS AvgOfAmount, Payment.Paymentatype
FROM Payment
GROUP BY Payment.Paymentdate, Payment.Paymentatype;
SELECT Product.ProdName, Product.UnitPrice, Stock.Quantity, Stock.ProdID
FROM Product INNER JOIN Stock ON Product.ProdID = Stock.ProdID
WHERE (((Stock.ProdID)=1 Or (Stock.ProdID)=2));
SELECT Stock.ProdID, Stock.StockDate, Sum(Stock.Quantity) AS SumOfQuantity
FROM Stock
GROUP BY Stock.ProdID, Stock.StockDate, Stock.ProdID;
SELECT Supplier.SuppLastName, Supplier.SuppPhone, Product.ProdName, Product.ProdType,
Stock.Quantity
FROM (Supplier INNER JOIN Product ON Supplier.SuppID = Product.SuppID) INNER JOIN Stock ON
Product.ProdID = Stock.ProdID;

Conclusion
As should be clear from this paper, modern commercial database systems are grounded both in
academic research and in the experiences of developing industrial-strength products for high-end
customers. The task of writing and maintaining a high-performance, fully functional relational DBMS
from scratch is an enormous investment in time and energy. Many of the lessons of relational DBMSs,
however, translate over to new domains. Web services, network-attached storage, text and e-mail
repositories, notification services, and network monitors can all benefit from DBMS research and
experience. Data-intensive services are at the core of computing today, and knowledge of database
system design is a skill that is broadly applicable, both inside and outside the halls of the main database
shops. These new directions raise a number of research problems in database management as well, and
point the way to new interactions between the database community and other areas of computing.