Anglia Ruskin University STU50613: Nova Cosmetics Database Design

This report presents a comprehensive database design solution for Nova Cosmetics, developed for an Anglia Ruskin University assignment (STU50613). It begins with a critical assessment of various database models, including file-based, hierarchical, and network models, outlining their respective advantages and disadvantages. The report then delves into the principles and structure of the relational database model, providing an example to illustrate its components. Furthermore, it includes a conceptual model representing the system's contents, followed by a logical model detailing the database schema and relationships. Finally, the report presents a physical model, mapping the logical design to a specific database system. The design incorporates considerations for data integrity, security, and scalability, aiming to provide an efficient and robust database solution for Nova Cosmetics.

Anglia Ruskin University
BSc Computing (Information systems)
Database design
Nova Cosmetics
STU50613
Steve Presland
(???)
1

Table of ContentsTask 1 – Database systems.................................................................................................3
a. Critically assess the different database models that you know about and summarise their
respective advantages and disadvantages....................................................................................3
ile based data processing systemF - .................................................................................................................3
Advantages of using a ile based data processing systemF - .............................................................................4
Disadvantages of using a ile based data processing systemF - ........................................................................4
iH erarchical based database system..............................................................................................................4
Advantages of using a hierarchical based database system..........................................................................5
Disadvantages of using a hierarchical based database system......................................................................5
etwork model database systemN ...................................................................................................................5
Advantages of using a etwork model database systemN ..............................................................................6
Disadvantages of using a etwork model database systemN ..........................................................................6
b. Using an example, discuss the principles and structure of the relational database mode....7
Relational Database system..............................................................................................................................7
Advantages of using a relational database system..........................................................................................8
Disadvantages of using a relational data base system.....................................................................................8Task 2 – Conceptual Model................................................................................................9
Task 3: Logical Model.......................................................................................................14
Task 4: Physical Model.....................................................................................................20
Bibliography.....................................................................................................................24
2

Task 1 – Database systems
(Word count = 1208)
a. Critically assess the different database models that you know about and
summarise their respective advantages and disadvantages.
File based data-processing system
A computer system store data in the form of bits and bytes over files that are structured in a
tree structure (Shroff, 2010).
1) Bit - The smallest form of data.
2) Byte - a collection of several bits which represents one character (e.g. letter, number).
3) Field - When combining bytes together into a while word then we create a field.
4) Record – When we group together fields like a student ,first and last name, his
modules, dates and his programme we create a record.
5) File - A group of similar records makes a file. a group of files makes a whole database
system.
The Data Hierarchy Louden & Louden (2017)
3

Advantages of using a File based data-processing system
1) No equipment/material - Eliminates the need to use equipment and/or materials for
storing information (pages, pencil, pen) and to use hand writing which consumes time
and considered costly.
2) Organized structure - Provides a way to insert, delete and update data through a
hierarchal file structure (Shroff, 2010) where all files are kept within folders.
3) Easy Data processing - Provides the ability to search for data within files by either
using an application or the command line tool of the operating system in some cases.
Disadvantages of using a File based data-processing system
1) Data redundancy – As each file is related to a specific program and cannot be
shared with other programs data will begin to grow as we will store the same data
several times on different files.
2) Data inconsistency – Because that the same data is spread on different files, some
information might be up-to-date while other files will not contain the latest
information.
3) Program data dependence – Each application will use its own file as its source
of data making it very hard to commit changes.
4) Lack of flexibility – Since the data is coupled with each application there is no
way to retrieve data easily and such an operation can take weeks (if not months).
5) Poor security – There is a minimum amount of control for security and it is very
hard to manage.
6) Lack of data sharing and availability – It is hard to share the same data with
other departments in other regions in a timely manner.
Hierarchical based database system
The Hierarchical database contains:
1) Segment - The smallest form of data that IMS could store.
2) Fields – Segment contain fields (the smallest piece of data the an application can
manipulate). Each field contains a unique key field that can be used to locate a
segment within the database
3) Record – The tree-structure of all segments is the database record while the root
segment identifies a database record with depended segment which contain pieces
of data that relevant for the record.
4

Example of a hierarchical structure (Powell, 2006):
Advantages of using a hierarchical based database system
1) Data Integrity – The IMS database provides a locking mechanism where only 1
application can write into the database at a time. Additionally the IMS is using a
logger that store changes made to the database by snapshots, which help restore data.
2) Security – By using the DRBC component the database can authorize requests from
an application.
3) Data sharing and availability – The DRBC also support concurrencies and allows
to share the data between several applications by using the DML interface.
Disadvantages of using a hierarchical based database system
1) Limited data independence – Same like with a file-based system, each application
will create its own depended data source and by that create challenges to update
information on a record for each record.
2) Lack of flexibility – Searching for information through the records will have to go
all the way from the root to the child’s in a recursive manner which will be a time
consuming operation. (Hoffer, et al., 2015) , additionally segments have fixed length
Network model database system
This system uses pointers that identifies the physical address of each record on the storage.
(Rai & Singh, 2015).
The model was designed to solve the lack of flexibility in the hierarchical database.
5

Example of a networking structure (Powell, 2006)
Advantages of using a Network model database system
1) Conceptual simplicity – easy to use and design
2) Support more relational types – Many-to-Many and one-to-many.
3) Ease of access – It is more easier to access data then in the hierarchical model.
4) Data independence – The network model separates the data from complex physical
storage information.
5) Meet Database Standards – The network model met the ANSI/SPARC (Thakur,
n.d.)
Disadvantages of using a Network model database system
1) System complexity – All the system relies on the pointers which makes it very
complex to manage.
2) Operational anomalies - Every insert, update and delete operation will require to
change many pointers which makes every operation difficult to perform.
3) Absence of structural independence – Making a change of the structural level is
very hard since the applications would have to be changed as well in order to work
with the database after such a change which raise another new data independence
challenges.
6

b. Using an example, discuss the principles and structure of the relational
database mode.
Relational Database system
Was invented In the early 70s by a Dr. Edgar Codd (‘Ted’ Codd), who was a British scientist
formulized the conceptual data modeling (Teorey, 2011) which used the Entity-relationship
(ER) approach and it is based on the relational algebra and uses unique values defined as keys
to make a relationship between two or more tables.
A database record consists of the following:
1) Tables – A grid of rows and columns which contains data on entities and its
attributes.
2) Columns – Contains simple and atomic data value (e.g. integer, text) for a record
and has a unique name.
3) Row – Provides number of columns.
4) Primary Key – A Unique key that represents a row.
5) Foreign Key – A pointer that leads to another primary key on a different table.
Example of an RDBMS structure (Laudon & Laudon, 2017) :
7

In the example above we have two tables – SUPPLIER and PART which consists of columns
and rows.
Each column represents an entity and its attributes for example the entity “Part_Number” has
three different attributes which are “Part_Name”, “Unit_Price” and “Supplier_Number”.
The column “Supplier_Number” is inherited in from the “SUPPLIER” table which provides
an example to the relational mode.
Advantages of using a relational database system
1) Data independence – The format of the data can be easily changed through a
schema located in the system catalog (Adrienne Watt and Watt, 2014).
2) Views – Allows to expose information differently and selectively to users.
3) Data sharing and availability – The database can be shared with users at the same
time through concurrency control.
4) Control of data redundancy – If designed properly, Each data item is stored in
only one place in the database and can be controlled by application programming.
5) Data Integrity - Controlled by constraints by rules that dictates what can be entered
into a column (e.g. size, type) and ACID compliancy (Pokorny, 2013).
6) Security – Provides the ability to control unauthorized access.
7) No Coding is needed – SQL uses its own language called SEQUEL.
Disadvantages of using a relational data base system
1) Scaling – RDBMS can only scale up and cannot be scaled out due to the
complexity of the consistency model used.
2) Price – The SQL server requires lots of resources hence it is expensive.
3) Difficult interface – The interface syntaxes are complex which can make it
difficult to use.
8

Task 2 – Conceptual Model
Figure 1: Conceptual ERD Model
(Source: Created by Author)
The conceptual model describes the contents of the system. Through the figure 1, it
can be understood that staff, retailer, order, product and manufacturer are the main contents
of the system. The business stakeholders like staff, retailer and manufacturer and data
architects like order and product is mentioned in the diagram. The proposed model consists of
one-to-many, many-to-one and many-to-many relationship. The diagram shows multiplicity,
cardinality and optionality.
The conceptual model illustrates that table has five tables that are interconnected with
each other. One staff will take the order and store it. A staff can store many order but it is not
necessary that all the registered staff to will take orders. One retailer can order many time.
Each order must be done by single retailer. Not every retailer will order products. A retailer
can order many products at one time. It is also true that many products can be ordered by
various retailers simultaneously. That is why the product and order has many-to-many
relationship among them. An order must contain the products that are listed. A product may
not be ordered in a whole lifetime of business. A manufacturer can provide many products. A
product is supplied by only one retailer.
9

Table Name: Retailer
Attribute Data
Type
Value Key Description Example
retailerNumber INT 5 Primary
Key
The unique retailer number
through which each row in
this table can be identified
uniquely
00000
retailerName Varchar 40 No The name of the retailer,
two retailer can have same
name
ABC
Wholesaler
retailerAddress Varchar 40 No The physical address of the
retailer that consists of
building number, street
name, city, State and zip
769
Amethyst
Drive
Lansing, MI
48906
retailerTelepho
neNumber
INT 10 No The contact number of the
retailer. The code will be
identified based on the state
mentioned in address
5173218505
maximumCred
it
Decima
l
10,2 No The maximum amount of
credit a retailer can have
from selected organization
15000.00
joinDate Date Defau
lt
No The Join date of the retailer.
This indicates the on which
retailer signed contract with
November
29, 2005
10

Nova Cosmetics
Table Name: Staff
Attribute Data
Type
Val
ue
Key Description Example
staffNumber INT 5 Primary
Key
The unique staff number
through which each row in
this table can be identified
uniquely
00000
staffName Varchar 40 No The name of the staff. Two
staff can have same name
Mamie B.
McLane
staffAddress Varchar 40 No The physical address of the
staff that consists of
building number, street
name, city, State and zip.
This is the current address
of the staff.
2579
Skinner
Hollow
Road
Huntington,
OR 97907
staffPosition Varchar 40 No The designation of the staff. 5418692501
staffSalary Decimal 10,2 No Salary of the staff per
annum.
5000.00
joinDate Date Defa
ult
No The Join date of the staff.
This indicates the on which
staff started working with
Nova Cosmetics
December
25, 1993
Table Name: Order
11

Attribute Data
Type
Val
ue
Key Description Example
orderNumber INT 5 Primary
Key
The unique order number
through which each row in
this table can be identified
uniquely
00000
orderDate Date Defa
ult
No The date on which, order is
placed.
December
25, 2017
productNumbe
r
INT 5 Primary
Key
The unique product number
through which each row in
this table can be identified
uniquely
00000
productType Varchar 40 No The type of the product. A
product can of single type.
Many products can be
categorized into one type.
Suns cream
unitPrice Decimal 10,2 No Cost of individual product. 35.30
productQuantit
y
INT 5 No The quantity of each
product bought
3
totalPrice Decimal 10,2 No Total price for each product
bought
105.90
totalCost Decimal 10,2 No Total amount a customer
will pay. The order cost is a
derived attribute. The cost
205.50
12