Ask a question from expert

Ask now

Database Design

Create a text file with SQL statements based on an ER Diagram and submit it via Blackboard.

30 Pages2273 Words61 Views
   

Added on  2022-12-29

About This Document

This document provides an overview of database design and its importance in running a business system. It explains the concept of Entity Relationship Diagram (ERD) and discusses the relationships between entities. The document also includes SQL queries for creating, populating, and manipulating a database.

Database Design

Create a text file with SQL statements based on an ER Diagram and submit it via Blackboard.

   Added on 2022-12-29

BookmarkShareRelated Documents
Running head: DATABASE DESIGN
Database Design
Name of the Student
Name of the University
Author Note
Database Design_1
DATABASE DESIGN
1
Table of Contents
Introduction................................................................................................................................2
Section 1.....................................................................................................................................2
Section 2.....................................................................................................................................7
Queries...................................................................................................................................7
Section 3...................................................................................................................................11
Section 4...................................................................................................................................13
Section 5...................................................................................................................................21
Queries.................................................................................................................................21
Conclusion................................................................................................................................28
References................................................................................................................................29
Database Design_2
DATABASE DESIGN
2
Introduction
A database helps a lot in running a business system by maintaining the information of
the whole system. The database is a collection of various data which is interrelated to each
other (Schloming 2013). The data are store into the tables called entities and every table is
consist of attributes or columns. These stored data is later retrieved, deleted or updates using
the database management system (Harger, Jones and Seabolt 2013). For developing a
database an Entity Relationship Diagram is generally used. An ERD is basically diagram that
shows the relationships between the entities of the database (Thalheim 2013). Later the
Database is developed according to the ERD to organise the relationships, cardinality,
integrity and optionality of the data (Kruse and Wells 2016). This report discusses with the
provided ERD and its description. In later section of the report a database is created,
populated and manipulated using SQL queries.
Section 1
Relationship
ERD is a data modelling technique to develop a software system using some diagrams
and notations (Coronel and Morris 2016). The ERD provides the database a structure,
abstraction and optimization. The interesting fact of the ERD is that it can be directly
transformed into a Database schema (Al-Masree 2015). As it is known that schema or ERD is
consist of mainly attributes, entities and relationship between them. For linking tables, the
concept of primary key and foreign key is required where the foreign key of any table
references the column value of the parent table where the column is defined as primary key
(Roberts, Stanfill and Studer 2019). In this ERD, the relationships can be seen through the
keys and concept of strong entity is also seen. During the creation of the table, it is
considered to assign the primary and foreign keys to the respective tables appropriately. Also
Database Design_3
DATABASE DESIGN
3
the use of constraints (check, foreign key) is also introduced (Lano and Kolahdouz-Rahimi
2013). The relationship between the entities can be described as follows:
One customer can have zero or many shoporders.
Each shoporder is associated with one and only one customer.
Each shoporder is assigned by one and only one salesperson.
One sales person can place zero or many shoporders.
One order in shoporder is related to zero or many orderlines.
One orderline is associated with one and only one shoporder.
Each orderline is associated with one and only one product.
One product can be associated with zero or more orderlines.
One product can be allocated in zero or many times.
One allocation is related to one and only one product.
One manager can handle zero or more allocations.
One allocation is associated with one and only one manager.
One allocation can have zero or more qualitycheck.
One quality check is associated with one and only one allocation.
Check constraint error Customer Table
insert into customer values (8, 'Milton','Hastings','X','3 Blue St, Kew');
Check constraint error Product Table
insert into product values (40, 'Cupboard',-99);
insert into product values (41, 'Bookcase',8765);
Database Design_4
DATABASE DESIGN
4
FK Error Shoporder Table
insert into shoporder values(66,null,5,29);
insert into shoporder values(67,null,9,26);
FK errors Orderline Table
insert into orderline values(2,2,200,49,40,1,23);
insert into orderline values(10,10,175,52,36,1,23);
List rows in all Tables
select * from product;
select * from salesperson;
Database Design_5
DATABASE DESIGN
5
select * from customer;
select * from shoporder;
Database Design_6

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Database Design and Use Report 2022
|31
|2129
|20

Database Management System | Report | Assignment
|7
|719
|50

Assignment Solution for Data Warehouse
|20
|3019
|43

Database Concepts Theory Assignment
|25
|2759
|26

Database Design and Implementation: ER-Model, Relational Schema, Constraints, and Business Intelligence
|5
|1023
|448

(solved) Organic Pastures Clinic
|5
|452
|395