Unit 18 – Database Design

[Date]

Oakarhtet
[company name]
Table of Contents
Features of a relational database.......................................................................................................................... 1
Entities.............................................................................................................................................................. 2
Attributes.......................................................................................................................................................... 2
Relationship...................................................................................................................................................... 2
Benefits of relational database......................................................................................................................... 2
Referential integrity and the purpose of primary keys..........................................................................................2
Explain about referential integrity.................................................................................................................... 2
Describe primary key and foreign keys............................................................................................................. 3
Purpose of primary keys and how they are used to build the relationships between tables............................3
Potential errors in the design and construction of a database..............................................................................3
Relational database for a specified user................................................................................................................4
Features of a relational database
A relational database is a collection of information that organizes data points with defined relationships for
easy access. In the relational database model, the data structures -- including data tables, indexes and views --
remain separate from the physical storage structures, enabling database administrators to edit the physical
data storage without affecting the logical data structure. (Lutkevich, 2023)

Entities
In a database management system (DBMS), an entity is a piece of data tracked and stored by the system. This
could be something as simple as a customer's name and address or more complex information such as an
order or invoice. An entity is typically composed of multiple attributes, the individual data that make up the
Entity. (Sugandhi, 2023)

Attributes
In a database management system (DBMS), an attribute is a piece of data that describes an entity. For
example, in a customer database, the attributes might be name, address, and phone number. In a product
database, the attributes might be name, price, and date of manufacture. (Sugandhi, 2023)

Relationship
A relationship, in the context of databases, is a situation that exists between two relational database tables
when one table has a foreign key that references the primary key of the other table. Relationships allow
relational databases to split and store data in different tables, while linking disparate data items. (Rouse, 2014)

Benefits of relational database

Benefits of relational database are Simplicity, Ease of Data Retrieval, Data Integrity, Flexibility, Normalization.

Referential integrity and the purpose of primary keys

Relational databases organize data in a standardized manner. All data is entered into tables, the so-called
relations. The tables are linked with one another to create connections between the data. The backbone of any
relational database is the primary and foreign keys. Read along to find out why they are so important.
Primary and foreign keys help create structure in relational databases.
A primary key ensures unique row identification. This results in faster sorting, searching, and querying
operations. (Sławińska, 2021)

Explain about referential integrity

The typical way to enforce referential integrity is through foreign key constraints. Another common way has to
do with developing ‘triggers’ that will implement ‘cascades’ throughout a database structure. (Rouse, 2021)

Describe primary key and foreign keys

A primary key is used to ensure that data in the specific column is unique. A column cannot have NULL values.
It is either an existing table column or a column that is specifically generated by the database according to a
defined sequence.
A foreign key is a column or group of columns in a relational database table that provides a link between data
in two tables. It is a column (or columns) that references a column (most often the primary key) of another
table. (SHUBHAMSINGH10, 2021)

Purpose of primary keys and how they are used to build the relationships between tables
A primary key constraint is a column that uniquely identifies every row in the table of the relational database
management system, while a foreign key is a column that creates a relationship between two tables. (Peterson,
2023)

Potential errors in the design and construction of a database

There are several errors that can occur from the design and construction of a database. How these can be
avoided depends mostly depends on the type of error and also how large it is.

The first and most common error that occurs from trying to design a database is inserting the incorrect
information in the database during the initial design process. This is quite a serious error to occur because it
will result in the database giving you the wrong data when you need it. The only way to avoid this is to take
extreme care when inputting the data into the system in the first place. Double and triple check all the
information that you put into the system. Human error is often considered to be the biggest cause of wrong
information input and although the error may seem small, the repercussions of it could be huge.

Another error is incorrect normalization of the data can lead to an unwanted duplication of the data which can
result in either small or huge data anomalies. Also, using incorrect types of data or indeed any incorrect input
of data can input the system's ability to create queries.

Here are some other errors that could occur when it comes to the design and construction of a database. The
accidental deleting of fields, tables or rows could have a series implication on the results that are given from
the database. The way to avoid this error is simple. Just take care not to delete any rows, tables or fields and if
you do then you can always hit the undo button as soon as it happens. Before you save any work, you should
always check that everything is in its right place and that nothing has disappeared. If you use an incorrect
method to re name any fields, rows, tables or columns then this could impact on the relationship of the data,
resulting in incorrect data being given back to you. (Facey, Anoy)

Relational database for a specified user

Manual Selling Problems are : Inventory Errors, Inaccurate Data, Slow Reporting, Customer Info Scattered,
Checkout Delays. We can use computerized system to avoid this problem. Computerized system benefits are
Inventory Management, Data Accuracy, Quick Reports, Customer Insights, Faster Checkout. We can use
appropriate software to produce the computerized system. For example : Inventory Control, Security, Sales
Reports, Customer Management.

+Unnormalized Form 1st Normal Form 2nd Normal Form 3rd Normal Form
Stock Stock StockID StockID
StockType StockType StockType StockType
StockPrice StockPrice StockPrice StockPrice
StockQuantity StockQuantity StockQuantity StockQuantity
StockDate StockDate StockDate StockDate
Sale Sale
SaleDiscount SaleDiscount SaleID SaleID
 SalePrice SalePrice SaleDiscount SaleDiscount
SalePerson SalePerson SalePrice SalePrice
SaleItemName SaleItemName SalePerson SalePerson
SaleTotalPrice SaleTotalPrice SaleItemName SaleItemName
Purchase Purchase SaleTotalPrice SaleTotalPrice
PurchaseItem PurchaseItem
PurchaseType PurchaseType PurchaseID PurchaseID
PurchaseDate PurchaseDate PurchaseItem PurchaseItem
Supplier Supplier PurchaseType PurchaseType
SupplierName SupplierName PurchaseDate PurchaseDate
SupplyDate SupplyDate
SupplyMaterial SupplyMaterial SupplierID SupplierID
SupplierInformation SupplierAddress SupplierName SupplierName
Customer SupplierPhoneNumber SupplyDate SupplyDate
CustomerName SupplierEmail SupplyMaterial SupplyMaterial
CustomerService Customer SupplierAddress SupplierAddress
Customerinformation CustomerName SupplierPhoneNumber SupplierPhoneNumber
CustomerService SupplierEmail SupplierEmail
CustomerPhoneNumber
CustomerID
CustomerName
CustomerService
CustomerPhoneNumber CustomerID
CustomerName
CustomerService
CustomerPhoneNumber

ER Diagram

SupplierID
Purchase
Item Purchase
Supplier Type
SupplyDate Name