Database Design and Implementation 2017

36
Database Design and Implementation 

 

Database Design and Implementation 

Database Design and Implementation. Assumptions, Conceptual Data Model Diagram, Logical Relational Schema, SQL Code, Relational DBMS Implementation.

1. Assumptions Database Design and Implementation :

1.1. Common assumptions database design and implementation  :
-The standard unit of currency is the dollar.
-The standard date format is MM/DD/YYYY.
1.2. Specific assumptions :
1.2.1. Part information:
-The part numbers follow company’s standard: Pxxxxx.  P00001 etc.
-The part description is simply its name with some basic information.
-The serial number kept the same from the manufacturer.
Reorder Level is a positive number. The quantity-on-hand of a part should always be greater than Reorder Level. Otherwise, a new order made  specific supplier. Reorder Quantity is a number of parts this order should accomplish.
-The unit of Power rating for electronic parts is Volt-Ampere (VA).
-The unit of Voltage for electronic parts is Volt (V).
-The physical dimension of mechanical parts, for simplicity, is a short description about the part’s size.  109 x 45.6 x 14.6 mm
-For easily keep track of part type, an attribute called PartType in Part entity to explain this. PartType = 0 is Mechanical Part, PartType = 1 is Electronic Part.
-A component can have a maximum of three levels of Subcomponents. This rule cannot be illustrated in ERD. However,  an attribute called “PartLevel” in Part entity which is used to explain this. PartLevel = 0 is a part that doesn’t have any component. PartLevel = 3  about a part has current level is 3 and this part cannot become a component of other parts.

1.2.2. Supplier information to design :
The supplier number follows company’s standard: Sxxxxx.  S00001 etc.
Only address, phone and fax number are required.  Supplier’s address is simply the description of the location of the supplier. No more attribute  required for detailed address items.  When the supplier that no longer required on the live system, it will be indicated by using available attribute: 1 for available and 0 for an unavailable or archived state.

1.2.3.  Employee information:
The standard email  used to unique identify the employee.
Email and Password are used for authorization.
Employee name and date of birth are required.

1.2.4. Order Information  :
– The order number follows company’s standard: ODxxxx.  OD0001 etc.
– Delivery Charge is simply a total payment for one-off delivery (the whole order).
– The total amount of an order is calculated by: Amount of each order-detail + Delivery Charge

2.  Conceptual Data Model Diagram – Database Design and Implementation : 

Database Design and Implementation 
Database Design and Implementation

Figure 1: Conceptual Data Model Diagram

3. Logical Relational Schema – Database Design and Implementation:

3.1. Part (PartNo, PartDescription, PartLevel, PartType, SerialNo, QOH, ReorderLevel,ReorderQuantity)
3.2. PartComponent (PartNo, ComponentNo)
/* PartNo referencing Part.PartNo, ComponentNo referencing Part.PartNo */
3.3. MechanicalPart (PartNo, Dimensions)
/* PartNo referencing Part.PartNo */
3.4. ElectronicPart (PartNo, PowerRating, Voltage)
/* PartNo referencing Part.PartNo */
3.5. Supplier (SupplierNo, SupplierName, SupplierAddress, Phone, Fax, Available)
3.6. SupplierPart (SupplierNo, PartNo)
/* SupplierNo referencing Supplier.SupplierNo; PartNo referencing Part.PartNo */
3.7. Employee (Email, Password, EmployeeName, DOB)
3.8. Order (OrderNo, SupplierNo, Email, OrderDate, DeliveryDate, DeliveryCharge, TotalAmount)
/* SupplierNo referencing Supplier.SupplierNo; Email referencing Employee.Email*/
3.9. OrderPart (OrderNo, PartNo, Quantity, UnitPrice)
/* OrderNo referencing Order.OrderNo; PartNo referencing Part.PartNo */

Database Design and Implementation 

 

                          Figure 2: relationship diagram sysytem

4. Normalisation Check 

The Relational Schema satisfies 3NF criteria.

5.  Relational DBMS Implementation 

 5.1. Part: This table is used to describe part DDAI.

no  PKPart number

Field name Data type Null Default Referencing Key Description
PartNo varchar(10)
PartDescription nvarchar(200) no Part name & information
PartLevel smallint no 0 Level of a part
PartType bit no 0 Type of a part
SerialNo varchar(50) no Manufacturer serial number
QOH smallint no 0 Quantity on hand
ReorderLevel smallint no 0 Reorder level
ReorderQuantity smallint no 0 Reorder quantity

5.2. PartComponent: This table is used to describe relationship of a part system and its child.

Field name Data type Null Default Referencing Key Description
PartNo varchar(10) no Part.PartNo PK, FK Part (parent) number
ComponentNo varchar(10) no Part.PartNo PK, FK Part (subcomponent) number

5.3. MechanicalPart: This table is used to describe mechanical part

Field name   Data type  Null  Default Referencing Key Description
PartNo varchar(10) no Part.PartNo PK, FK Part number
Dimensions varchar(50) no Part physical dimensions

5.4. ElectronicPart: This table is used to describe electronic part

5.6. SupplierPart: This table is used to describe relationship between a supplier and its supplied parts

Database Design and Implementation 

Note: We all use MS SQL Server  to do the above

6.  SQL Code – Database Design And Implementation :  
6.1. SQL code for A1:

SELECT S.SupplierName AS ‘Supplier Name’,
S.SupplierAddress AS ‘Supplier Address’,
COUNT (EP.PartNo) AS ‘Total number of Electronic Parts’
FROM Supplier S, SupplierPart SP, Part P, ElectronicPart EP
WHERE (S.SupplierNo = SP.SupplierNo AND S.Available = 1
AND SP.PartNo = P.PartNo AND P.PartNo = EP.PartNo)
GROUP BY S.SupplierName, S.SupplierAddress

6.2. SQL code for A2:

SELECT S.SupplierNo AS ‘Supplier Number’, S.SupplierName AS ‘Supplier Name’,S.SupplierAddress AS ‘Supplier Address’, S.Phone, S.Fax,

(CONVERT(CHAR(10), MAX(O.OrderDate),101)) AS ‘Date of the last order’

FROM Supplier S, [Order] O

WHERE (S.SupplierNo = O.SupplierNo AND Available = 0)

GROUP BY S.SupplierNo, S.SupplierName, S.SupplierAddress, S.Phone, S.Fax

Noted: “Archived suppliers” are suppliers that no longer required on the live system.

 

6.3. SQL code for A3:

SELECT ‘Electronic Part’ AS ‘Category of Part’,

COUNT(DISTINCT EP.PartNo) AS ‘Total components’,

COUNT(DISTINCT S1.SupplierNo) AS ‘Total suppliers’

FROM Supplier S1, SupplierPart SP1, Part P1, ElectronicPart EP

WHERE (S1.SupplierNo = SP1.SupplierNo AND S1.Available = 1

AND SP1.PartNo = P1.PartNo AND P1.PartNo = EP.PartNo)

UNION ALL

SELECT ‘Mechanical Part’ AS ‘Category of Part’,

COUNT(DISTINCT MP.PartNo) AS ‘Total components’,

COUNT(DISTINCT S2.SupplierNo) AS ‘Total suppliers’

FROM Supplier S2, SupplierPart SP2, Part P2, MechanicalPart MP

WHERE (S2.SupplierNo = SP2.SupplierNo AND S2.Available = 1

AND SP2.PartNo = P2.PartNo AND P2.PartNo = MP.PartNo)

 

6.4. SQL code for A4 [1] – With Part Number is ‘P00001’:

WITH Temp_ComponentPart (PartNo,ComponentNo, iteration) AS (

SELECT PartNo, ComponentNo, 0

FROM ComponentPart WHERE PartNo = ‘P00001’

UNION ALL

SELECT T1.PartNo, CP.ComponentNo, T1.iteration + 1

FROM Temp_ComponentPart T1, ComponentPart CP

WHERE T1.ComponentNo = CP.PartNo

) SELECT T2.ComponentNo AS ‘Part Number’, P.PartDescription AS ‘Part Description’,

P.PartLevel AS ‘Part Level’, P.SerialNo AS ‘Serial Number’

FROM Temp_ComponentPart T2, Part P

WHERE P.PartNo = T2.ComponentNo

6.5. SQL codes for A5: 

Coursework

– The query to populate data into the Report, using the following sample data

SupplierName SupplierNo Given Date
T&T Hardware Ltd S00001 7/07/2016

SELECT O.OrderNo AS ‘Order Number’,

 

 

(CONVERT(VARCHAR,(CONVERT(MONEY, O.TotalAmount))) + ‘ $’) AS ‘Total Amount’,

(CONVERT(CHAR(10), O.OrderDate,101)) AS ‘Order Date’,

(CONVERT(CHAR(10), O.DeliveryDate,101)) AS ‘Delivery Date’,

(CONVERT(VARCHAR, (CONVERT(MONEY, O.DeliveryCharge))) + ‘ $’) AS ‘Delivery Charge’,

S.SupplierName AS ‘Supplier Name’, E.EmployeeName AS ‘Employee Name’

FROM [Order] O, Supplier S, Employee E

WHERE (O.SupplierNo = S.SupplierNo AND O.Email = E.Email)

AND O.SupplierNo=’S00001′

AND O.DeliveryDate > ‘7/07/2016’

6.6. SQL codes for A6: 

– The query to populate data into the Master list, using the following sample data:

EmployeeName Email Given Date
David Smith E00001@mm.co.uk 7/07/2016

SELECT O.OrderNo AS ‘Order Number’,

(CONVERT(VARCHAR,(CONVERT(MONEY, O.TotalAmount))) + ‘ $’) AS ‘Total Amount’,

(CONVERT(CHAR(10), O.OrderDate,101)) AS ‘Order Date’,

(CONVERT(CHAR(10), O.DeliveryDate,101)) AS ‘Delivery Date’,

(CONVERT(VARCHAR, (CONVERT(MONEY, O.DeliveryCharge))) + ‘ $’) AS ‘Delivery Charge’,

S.SupplierName AS ‘Supplier Name’, E.EmployeeName AS ‘Employee Name’

FROM [Order] O, Supplier S, Employee E

WHERE (O.SupplierNo = S.SupplierNo AND S.Available = 1 AND O.Email = E.Email)

AND O.Email=’E00001@mm.co.uk’

AND O.OrderDate > ‘7/07/2016’

– The query to populate data into the Details list, with order number is ‘OD00001’:

SELECT P.PartNo AS ‘Part Number’, P.PartDescription AS ‘Part Description’,

P.SerialNo AS ‘Serial Number’, OP.Quantity,

(CONVERT(VARCHAR,(CONVERT(MONEY, OP.UnitPrice))) + ‘ $’) AS ‘Unit Price’,

(CONVERT(VARCHAR,(CONVERT(MONEY, (OP.UnitPrice*OP.Quantity)))) + ‘ $’) AS ‘Amount’

FROM OrderPart OP, Part P

WHERE (OP.PartNo = P.PartNo AND OP.OrderNo=’OD00001′)

6.7. SQL code for D7: 

Insert new supplier, the following sample data into the database:

INSERT INTO Supplier (SupplierNo, SupplierName, SupplierAddress, Phone, Fax) VALUES (‘S00009’, ‘MXI Hardware Ltd.’, ’76 Drayton Park, London, UK’, ’20 77774040′, ’20 7777 4041′)

SupplierNo SupplierName Supplier Address Phone  Fax
S00009 MXI Hardware Ltd. 76 Drayton Park, London, UK 20 7777 4040 20 7777 4041

 

 

Database Design and Implementation 

Database Design and Implementation 

Database Design and Implementation 

 

Database Design and Implementation 

 

 

Database Design and Implementation 

 

 

Database Design and Implementation 
Database Design and Implementation
Database Design and Implementation 
Database Design and Implementation
  1. References:

Computer, software free,  introduction,  product  systems database, database introduction,sign, views, load, views database customer, books free, amazon, learning, database data, books database.

SQL Server 2012 Books Online (November 2015), “Recursive Queries Using Common Table

Expressions”, Microsoft TechNet – SQL Server TechCenter.

 

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here