UShop is an online retail company and the business has grown immensely over the years. In order to manage the data and information sets, the company is looking to Relational Database Management System (RDBMS). The database needs to be developed for the organization and there are certain parameters that must be met. The database must store the information of the customers, products, employees, and orders associated with the company. There are specific attributes associated with the four major categories of information.
There are also certain conditions that need to be met while developing the database. For instance, an employee of UShop can server multiple orders at the same time while one order shall be served by a single employee only. Similarly, an order may comprise of one or more products and one product can be ordered in multiple orders. The development team will be able to adequately develop and implement the database with the aid of the database details, such as normalization, entity relationship diagrams, details of the attribute, and likewise.
Understanding the Database
A database is a container in which is used in order to store and organize the information sets. There are manipulations that can be made to the database as per the user role that is assigned. In order to implement all of these capabilities, it is required to implement a specialized engine called a Database Management System (DBMS). The database engine is composed of the software, hardware, and the information sets. There are different types of database management systems that have been designed. File management system is one of the types of database management systems in which file-based processing is carried out.
The database management system that is to be implemented for UShop is Relational Database Management System (RDBMS). It is the database that is designed on the basis of the relational model and was developed by E.F. Codd in the year 1970. The basic format which may be used to explain the database is the tables that are made up of rows and columns. The software development process can be broadly categorized and carried out through two major approaches.
One of these approaches is to break the problem in smaller pieces and add further details to each sub-level (Yamamoto and Koizumi, 2013). It is termed as the top-down or decomposition approach. The second approach is to develop the structure using a defined coding process and to carry out the testing of each of these steps. It is termed as the bottom-up approach. Code reusability is one of the prime benefits that are provided with the use of this approach.
The implementation of RDBMS will offer several advantages to the organization, such as:
?The table format that is used and supported by the RDBMSs is easy to use and understand. The users of the database will be the employees of UShop and they will get hold of the same in no time. It will lead to the enhancement of their respective productivity and efficiency levels (Seo and Kim, 2011).
?The database will allow multiple users to access the database at the same time. The database will have transaction management facility implemented in it along with the build-in locking mechanism. This will ensure that the reliability of the information is maintained at every instance and there are no performance issues during simultaneous database access by more than one user.
?There will be several users that will be provided with the access to the database. However, the access rights and permissions will vary from one user to the other. For example, the rights of an operational staff members and the director of UShop will be different. RDBMS will allow the database administrator to easily grant and control the data access which will lead to enhanced security and protection of the data sets (Hussain and Khan, 2014).
?The database administrator will be able to easily handle and maintain the database with the aid of automated maintenance tools. The backing up of the data sets will also be done with ease which will enhance the security of the data sets.
?Generic language as Structured Query Language (SQL) will be supported by the database. The syntax used in the database will be simple and it will allow easy execution of data handling and data storage operations.
Conceptual modellingPossible entities On the basis of given scenario, following entities are identified:
?CITY
?CUSTOMER
?EMPLOYEE
?ORDER
?ORDER_PRODUCT
?POTCODE
?PRODUCT
?QUALIFICATION
?STATE
An Entity Relationship (ER) model or diagram is the representation of the database entities and their relationship in a graphical format. There are four major entities that will be involved in the database of UShop and these will be Customers, Employees, Orders, and Products. Each of these entities will have their own set of attributes that will be represented in the model along with the relationship between the attributes.
Normalization is a design technique that is used in the databases that allows easier and efficient organization of the data sets to remove the redundancy and dependency present among the sets. The methodology that is followed in the technique is to divide the larger tables in to smaller ones to achieve the goals and then link the smaller tables with the aid of the relationships.
Edgar Codd came up with this technique and introduced the first normal form for avoiding and removing the redundancy in the data sets. Soon after, second and third normal forms were developed. Codd along with Boyce introduced another normal form which is referred as Boyce-Codd normal form. There are certain conditions that need to be fulfilled in order to make sure that the normal forms of the database are achieved (Sharmila and Anusya, 2012).
For the first normal form, it is essential that each table cell has a single value and every record is unique in nature. In the databases, there are keys that are used to uniquely identify a specific cell or record. Primary keys are used in the process of normalizing the database and these refer to a single column value that must be unique for every record.
Composite keys are also used in the process and these are made up of multiple columns. In the second normal form, it is necessary that the table is already present in the first normal form and single columns primary key is defined and is present (Attallah, 2017). Foreign keys are also used in the process of normalization. These are the keys that reference the primary key from another table. Transitive functional dependency is defined as a condition wherein the changes in a non-key column may change or have an impact on other non-key column.
The database is considered to be in its third normal form when it is already present in its second normal form and there are no transitive functional dependencies present (Demba, 2013). There are chances that the database may be in its third normal form and there may be certain anomalies present in the database. Boyce Codd Normal Form (BCNF) is the normal form which is then applied to deal with the anomalies.
All the entities shown in figure 2 are in UNF. I started with splitting all entities into smaller tables and then established relationships among them.
?1NF – In order to convert UNF into 1NF, I followed the rule that the data should atomic i.e. each cell should contain only single value and identified primary key (attribute that uniquely identifies other attribute values. It is always Non-NULL). In this process, foreign keys are also identified which are used for connecting other tables by referencing primary key of another table.
?2NF – In this process, repeating groups are eliminated using primary key. This was done by creating a separate table with unique primary key.
?3NF – In this process, transitive dependency is removed.
Relational schema
CUSTOMER – {customerID, customerFirstName, customerLastName, customerOtherName, houseNumber, streetName, cityID, postCodeID, contactNumber}
STATE – {stateID, stateName}
CITY – {cityID, cityName, stateID}
POSTCODE – {postCodeID, postcode, cityID}
QUALIFICATION – {qualificationID, qualificationName}
EMPLOYEE – {employeeID, employeeFirstName, employeeLastName, houseNumber, streetName, cityID, postCodeID, salary, jobTitle, contactNumber}
ORDERS – {orderID, orderDate, totalQuantity, customer, employee}
PRODUCT – {productID, productName, productPrice, productStock}
ORDER_PRODUCT – {orderID, productID, quantity}
Physical model
Creation script
CREATE TABLE STATE
(stateID INT AUTO_INCREMENT
, stateName VARCHAR(20) NOT NULL
, PRIMARY KEY (stateID)
) ;
CREATE TABLE CITY
(cityID INT AUTO_INCREMENT
, cityName VARCHAR(20) NOT NULL
, state INT NOT NULL
, PRIMARY KEY (cityID)
) ;
ALTER TABLE CITY ADD CONSTRAINT city_state_fk FOREIGN KEY (state) REFERENCES STATE (stateID);
CREATE TABLE POSTCODE
(postCodeID INT AUTO_INCREMENT
, postCode INT NOT NULL
, city INT NOT NULL
, PRIMARY KEY (postCodeID)
) ;
ALTER TABLE POSTCODE ADD CONSTRAINT postCode_city_fk FOREIGN KEY (city) REFERENCES CITY (cityID);
CREATE TABLE QUALIFICATION
(qualificationID INT AUTO_INCREMENT
, qualificationName VARCHAR(30) NOT NULL
, PRIMARY KEY (qualificationID)
CREATE TABLE CUSTOMER (
customerID INT AUTO_INCREMENT
, customerFirstName CHAR(30) NOT NULL
, customerLastName CHAR(30) NOT NULL
, customerOtherName CHAR(30) NOT NULL
, houseNumber INT NOT NULL
, streetName VARCHAR(20)
, city INT
, postCode INT
, contactNumber INT
, PRIMARY KEY (customerID)
);
ALTER TABLE CUSTOMER ADD CONSTRAINT customer_city_fk FOREIGN KEY (city) REFERENCES CITY (cityID);
ALTER TABLE CUSTOMER ADD CONSTRAINT customer_postCode_fk FOREIGN KEY (postCode) REFERENCES POSTCODE (postCodeID);
CREATE TABLE EMPLOYEE (
employeeID INT AUTO_INCREMENT
, employeeFirstName CHAR(30) NOT NULL
, employeeLastName CHAR(30) NOT NULL
, houseNumber INT NOT NULL
, streetName VARCHAR(20)
, city INT
, postCode INT
, salary int
, jobTitle VARCHAR(20)
, contactNumber INT
, PRIMARY KEY (employeeID)
);
ALTER TABLE EMPLOYEE
ADD qual INT;
ALTER TABLE EMPLOYEE ADD CONSTRAINT employee_qual_fk FOREIGN KEY (qual) REFERENCES QUALIFICATION (qualificationID);
ALTER TABLE EMPLOYEE ADD CONSTRAINT employee_city_fk FOREIGN KEY (city) REFERENCES CITY (cityID);
ALTER TABLE EMPLOYEE ADD CONSTRAINT employee_postCode_fk FOREIGN KEY (postCode) REFERENCES POSTCODE (postCodeID);
CREATE TABLE ORDERS (
orderID INT AUTO_INCREMENT
, orderDate DATE NOT NULL
, totalQuantity INT NOT NULL
, customer INT NOT NULL
, employee INT NOT NULL
, PRIMARY KEY (orderID)
);
ALTER TABLE ORDERS ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer) REFERENCES CUSTOMER (customerID);
ALTER TABLE ORDERS ADD CONSTRAINT orders_employee_fk FOREIGN KEY (employee) REFERENCES EMPLOYEE (employeeID);
CREATE TABLE PRODUCT (
productID INT AUTO_INCREMENT
, productName VARCHAR(30) NOT NULL
, productPrice INT NOT NULL
, productStock INT NOT NULL
, PRIMARY KEY (productID)
);
CREATE TABLE ORDER_PRODUCT (
productID INT NOT NULL
, orderID INT NOT NULL
, quantity INT NOT NULL
, PRIMARY KEY (productID, orderID)
);
ALTER TABLE ORDER_PRODUCT ADD CONSTRAINT orderP_product_fk FOREIGN KEY (productID) REFERENCES PRODUCT (productID);
ALTER TABLE ORDER_PRODUCT ADD CONSTRAINT orderP_order_fk FOREIGN KEY (orderID) REFERENCES ORDERS (orderID);
ER diagram in phpMyAdmin
Figure 4: Table structure in PhpMyAdmin
Table structureInsertion script
STATE TABLE–
INSERT INTO STATE VALUES (3, ‘STATE3’), (4, ‘STATE4’), (5, ‘STATE5’), (6, ‘STATE6’), (7, ‘STATE7’)
CITY TABLE –
INSERT INTO CITY VALUES (1,’CITY1′,1), (2,’CITY2′,2), (3,’CITY3′,3), (4,’CITY4′,4),(5,’CITY5′,5)
POSTCODE TABLE –
INSERT INTO POSTCODE VALUES (1, 1111, 1), (2, 1112, 1), (3, 2222, 2), (4, 3333, 3), (5, 7777, 4)
QUALIFICATION TABLE –
INSERT INTO qualification VALUES (1, ‘QUALIFICATION 1’), (2, ‘QUALIFICATION 2’), (3, ‘QUALIFICATION 3’), (4, ‘QUALIFICATION 4’), (5, ‘QUALIFICATION 5’)
CUSTOMER TABLE –
INSERT INTO CUSTOMER (`customerID`,`customerFirstName`,`customerLastName`, `customerOtherName`, `houseNumber`, `streetName`, `city`, `postCode`, `contactNumber`) VALUES (1, ‘JOHN’, ‘RAY’, ”, 123, ‘STREET 1’, 1, 1, 123456),
(2, ‘JAMES’, ‘RAY’, ”, 13, ‘STREET 2’, 2, 2, 223456),
(3, ‘RITA’, ‘RAY’, ”, 23,’STREET 3′, 3, 2, 523456),
(4, ‘JILLY’, ‘RAY’, ”, 12, ‘STREET 4’, 4, 3, 723456),
(5, ‘FERENDES’, ‘RAY’, ”, 113, ‘STREET 5’,5, 3, 923456)
EMPLOYEE TABLE –
INSERT INTO EMPLOYEE (`employeeID`, `employeeFirstName`, `employeeLastName`, `houseNumber`, `streetName`, `city`, `postCode`, `salary`, `jobTitle`, `contactNumber`, `qual`) VALUES
(1, ‘PETER’ , ‘GILL’ , 1234, ‘NORTH STREET’, 1, 1, 20000, ‘MANAGER’,5678, 1),
(2, ‘GIYA’ , ‘BILL’ , 234, ‘WEST STREET’, 2, 1, 1000, ‘SALES PERSON’, 35678, 2),
(3, ‘JOB’ , ‘GATES’ , 34, ‘EAST STREET’, 3, 1, 3000, ‘SALES’, 34678, 3),
(4, ‘STEVE’ , ‘BILL’ , 14, ‘NORTH WEST STREET’, 4, 1, 12000, ‘TEAM LEAD’, 34568, 1),
(5, ‘TOM’ , ‘COOK’ , 124, ‘SOUTH STREET’, 1, 1, 12000, ‘ASST. MANAGER’, 345611, 1)
PRODUCT TABLE –
INSERT INTO PRODUCT (`productID`, `productName`, `productPrice`, `productStock`) VALUES (1, ‘PRODUCT 1’, 22, 10), (2, ‘PRODUCT 2’, 122, 20), (3, ‘PRODUCT 3’, 212, 30), (4, ‘PRODUCT 4’, 332, 40), (5, ‘PRODUCT 5’, 112, 50)
ORDERS TABLE –
INSERT INTO ORDERS (`orderID`, `orderDate`, `totalQuantity`, `customer`, `employee`) VALUES (1, ‘2018-11-01’, 12, 1,1), (2, ‘2018-11-01’, 32, 2,1), (3, ‘2018-10-21’, 2, 3,2), (4, ‘2018-09-11’, 12, 4,3), (5, ‘2018-08-11’, 22, 5,4)
ORDER_PRODUT TABLE –
INSERT INTO order_product (`productID`, `orderID`, `quantity`) VALUES (1,1, 12), (1,2, 10), (2,2, 22), (3,3, 2), (4,4, 12)
Conclusion
UShop will be able to gain several benefits with the implementation of relational database management system. The data sets will be stored and organized in a better way along with the enhancement of reliability and security of the information. There are several security risks that the information sets are exposed to. With the implementation of RDBMS, the authentication and authorization schemes and features will get associated with the database. It will allow the protection of the data sets from the information security and privacy attacks.
References
Attallah, B. (2017). Simplifying Database Normalization within a Visual Interactive Simulation Model. International Journal of Database Management Systems, 9(3), pp.57-69.
Demba, M. (2013). Algorithm for Relational Database Normalization Up to 3NF. International Journal of Database Management Systems, 5(3), pp.39-51.
Hussain, A. and Khan, M. (2014). Discovering Database Replication Techniques in RDBMS. International Journal of Database Theory and Application, 7(1), pp.93-102.
Seo, N. and Kim, S. (2011). Design, Implementation, and Performance Evaluation of an Embedded RDBMS Miracle. Journal of the Korea Academia-Industrial cooperation Society, 12(7), pp.3227-3235.
Sharmila, S. and Anusya, A. (2012). Database Design for Scheduling System using Normalization and SQL. Paripex – Indian Journal Of Research, 3(7), pp.1-4.
Yamamoto, M. and Koizumi, H. (2013). An Experimental Evaluation of Distributed Data Stream Processing using Lightweight RDBMS SQLite. IEEJ Transactions on Electronics, Information and Systems, 133(11), pp.2125-2132.
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download