SQL Database
Using pgAdmin III I created a detailed PostgreSQL database for a prospective specialty keyboard building company. This includes a customer database, detailed listings of keyboard parts such as cases, switches, PCBs, keycaps and the appropriate relationships between them.
The database was created by defining the different tables, their fields, restrictions and relationships. This code is outlined in the Data Definition Language (DDL) file. The other file, Data Manipulation Language (DML) is used to populate the database for testing purposes.
A picture demonstrating the database as an Entity Relationship Diagram and the code is shown below:
Tap to expand
Source Code
1 -- FileName: Assignment2-DDL.sql
2 -- Description: script to create the Inventory II database as required in Assignment 1, create the tables from the inventory database and the country-city database
3 -- CST 8215
4 -- Author: Mostapha Abdelaziz
5 -- Year: 2020
6 -- Last Modified: 30 Nov 2020
7
8 --dropping the views if they exist to allow tables to be dropped
9 DROP VIEW IF EXISTS Order_V;
10 DROP VIEW IF EXISTS Threepins_V;
11 --dropping the tables if they exist, allows this script to be run multiple times without errors
12 DROP TABLE IF EXISTS Invoice_Line_T;
13 DROP TABLE IF EXISTS Invoice_T;
14 DROP TABLE IF EXISTS Customer_T;
15 DROP TABLE IF EXISTS Keycaps_T;
16 DROP TABLE IF EXISTS Switches_T;
17 DROP TABLE IF EXISTS Pcb_T;
18 DROP TABLE IF EXISTS Case_T;
19 DROP TABLE IF EXISTS Layout_T;
20
21 --creating the Layout Table
22 CREATE TABLE Layout_T(
23 Layout VARCHAR( 30 ),
24 Switches INTEGER NOT NULL,
25 CONSTRAINT PK_Layout PRIMARY KEY( Layout )
26 );
27
28 --creating the Case Table
29 CREATE TABLE Case_T (
30 Case_Id CHAR(6),
31 Case_Layout VARCHAR( 30 ) NOT NULL,
32 Case_Material VARCHAR( 30 ),
33 Case_Cost DECIMAL(5, 2),
34 Case_Quantity INTEGER,
35 CONSTRAINT PK_Case_Id PRIMARY KEY( Case_Id ),
36 CONSTRAINT FK_Case_Layout FOREIGN KEY( Case_Layout ) REFERENCES Layout_T( Layout )
37 );
38
39 --creating the PCB Table
40 CREATE TABLE Pcb_T (
41 Pcb_Id CHAR(6),
42 Pcb_Layout VARCHAR( 30 ) NOT NULL,
43 Led BOOLEAN,
44 Hotswap BOOLEAN,
45 Pcb_Pins INTEGER NOT NULL,
46 Pcb_Cost DECIMAL(5, 2),
47 Pcb_Quantity INTEGER,
48 CONSTRAINT PK_Pcb_Id PRIMARY KEY( Pcb_Id ),
49 CONSTRAINT FK_Case_Layout FOREIGN KEY( Pcb_Layout ) REFERENCES Layout_T( Layout )
50 );
51
52 --creating the Switches Table
53 CREATE TABLE Switches_T(
54 Switches_Id CHAR(6),
55 Switches_Name VARCHAR( 30 ),
56 Type VARCHAR( 10 ) NOT NULL,
57 Switches_Pins INTEGER NOT NULL,
58 Switches_Cost DECIMAL(5, 2),
59 Switches_Quantity INTEGER ,
60 CONSTRAINT PK_Switches_ID PRIMARY KEY( Switches_Id )
61 );
62
63 --creating the Keycaps Table
64 CREATE TABLE Keys_T(
65 Keys_Id CHAR(6),
66 Keys_Layout VARCHAR( 30 ) NOT NULL,
67 Keys_Name VARCHAR( 30 ) NOT NULL,
68 Keys_Material VARCHAR( 30 ),
69 Keys_Cost DECIMAL(5, 2),
70 Keys_Quantity INTEGER,
71 CONSTRAINT PK_Keys_ID PRIMARY KEY( Keys_Id ),
72 CONSTRAINT FK_Case_Layout FOREIGN KEY( Keys_Layout ) REFERENCES Layout_T( Layout )
73 );
74
75 --creating the Customer Table
76 CREATE TABLE Customer_T (
77 Cust_Id CHAR( 6 ),
78 Cust_Fname VARCHAR( 30 ) NOT NULL,
79 Cust_Lname VARCHAR( 30 ) NOT NULL,
80 Cust_Email VARCHAR( 30 ) NOT NULL,
81 Cust_Address VARCHAR( 20 ) NOT NULL,
82 Cust_PostCode CHAR( 6 ) NOT NULL,
83 CONSTRAINT PK_Customer PRIMARY KEY( Cust_Id )
84 );
85
86 --creating the Invoice Table
87 CREATE TABLE Invoice_T (
88 Invoice_Number CHAR( 6 ),
89 Cust_Id CHAR( 6 ) NOT NULL,
90 Invoice_Date DATE DEFAULT NOW(),
91 CONSTRAINT PK_Invoice PRIMARY KEY( Invoice_Number ),
92 CONSTRAINT FK_Cust_ID FOREIGN KEY( Cust_Id ) REFERENCES Customer_T( Cust_ID )
93 );
94
95 --creating the Invoice Line Table
96 CREATE TABLE Invoice_Line_T (
97 Invoice_Number CHAR( 6 ),
98 Invoice_Line INTEGER,
99 Case_Id CHAR( 6 ),
100 Pcb_Id CHAR( 6 ),
101 Switches_Id CHAR( 6 ),
102 Keys_Id CHAR( 6 ),
103 Hours INTEGER,
104 CONSTRAINT PK_Invoice_Line PRIMARY KEY( Invoice_Number, Invoice_Line ),
105 CONSTRAINT FK1_Invoice_Number FOREIGN KEY( Invoice_Number ) REFERENCES Invoice_T( Invoice_Number ),
106 CONSTRAINT FK2_Case FOREIGN KEY( Case_Id ) REFERENCES Case_T( Case_Id ),
107 CONSTRAINT FK3_Pcb FOREIGN KEY( Pcb_Id ) REFERENCES Pcb_T( Pcb_Id ),
108 CONSTRAINT FK4_Switches FOREIGN KEY( Switches_Id ) REFERENCES Switches_T( Switches_Id ),
109 CONSTRAINT FK5_Keys FOREIGN KEY( Keys_Id ) REFERENCES Keys_T( Keys_Id )
110 );
111
112 --creating two views
113 CREATE VIEW ORDER_V
114 AS
115 SELECT * FROM Invoice_T NATURAL JOIN Invoice_Line_T
116 WHERE Invoice_T.Invoice_Number = Invoice_Line_T.Invoice_Number;
117
118 CREATE VIEW Threepins_V
119 AS
120 SELECT * FROM Pcb_T NATURAL JOIN Switches_T
121 WHERE Pcb_T.Pcb_Pins = Switches_T.Switches_Pins;
122
123 -- eof Assignment2-DDL.sql
1 -- FileName: Assignment2-DML.sql
2 -- Description: script to populate the tables created from the DDL file
3 -- CST 8215
4 -- Author: Mostapha Abdelaziz
5 -- Year: 2020
6 -- Last Modified: 30 NOV 2020
7
8 --emptying the tables to allow this script to be run multiple times without duplicate values
9 DELETE FROM Invoice_Line_T;
10 DELETE FROM Invoice_T;
11 DELETE FROM Customer_T;
12 DELETE FROM Keys_T;
13 DELETE FROM Switches_T;
14 DELETE FROM Pcb_T;
15 DELETE FROM Case_T;
16 DELETE FROM Layout_T;
17
18 --populate the Layout Table
19 INSERT INTO Layout_T( Layout, Switches )
20 VALUES( 'STANDARD 75', 75);
21 INSERT INTO Layout_T( Layout, Switches )
22 VALUES( 'STANDARD 100', 100);
23 INSERT INTO Layout_T( Layout, Switches )
24 VALUES( 'COMPACT 60', 60);
25 INSERT INTO Layout_T( Layout, Switches )
26 VALUES( 'ORTHOLINEAR', 50);
27 INSERT INTO Layout_T( Layout, Switches )
28 VALUES( 'SPLIT 80', 80);
29
30 --populate the Case Table
31 INSERT INTO Case_T( Case_Id, Case_Layout, Case_Material, Case_Cost, Case_Quantity )
32 VALUES( 'C0001A', 'STANDARD 75', 'ACRYLIC', 150.00, 11 );
33 INSERT INTO Case_T( Case_Id, Case_Layout, Case_Material, Case_Cost, Case_Quantity )
34 VALUES( 'C0002A', 'STANDARD 100', 'ALUMINUM', 170.00, 7 );
35 INSERT INTO Case_T( Case_Id, Case_Layout, Case_Material, Case_Cost, Case_Quantity )
36 VALUES( 'C0003A', 'ORTHOLINEAR', 'ACRYLIC', 120.00, 20 );
37 INSERT INTO Case_T( Case_Id, Case_Layout, Case_Material, Case_Cost, Case_Quantity )
38 VALUES( 'C0004A', 'COMPACT 60', 'COPPER', 140.00, 15 );
39 INSERT INTO Case_T( Case_Id, Case_Layout, Case_Material, Case_Cost, Case_Quantity )
40 VALUES( 'C0005A', 'SPLIT 80', 'STEEL', 130.00, 12 );
41
42 --populate the Pcb Table
43 INSERT INTO Pcb_T( Pcb_Id, Pcb_Layout, Led, Hotswap, Pcb_Pins, Pcb_Cost, Pcb_Quantity )
44 VALUES( 'P0001A', 'STANDARD 75', '0', '1', 5, 121.99, 20 );
45 INSERT INTO Pcb_T( Pcb_Id, Pcb_Layout, Led, Hotswap, Pcb_Pins, Pcb_Cost, Pcb_Quantity )
46 VALUES( 'P0002A', 'STANDARD 100', '1', '1', 3, 130.99, 20 );
47 INSERT INTO Pcb_T( Pcb_Id, Pcb_Layout, Led, Hotswap, Pcb_Pins, Pcb_Cost, Pcb_Quantity )
48 VALUES( 'P0003A', 'STANDARD 75', '0', '1', 5, 111.99, 11 );
49 INSERT INTO Pcb_T( Pcb_Id, Pcb_Layout, Led, Hotswap, Pcb_Pins, Pcb_Cost, Pcb_Quantity )
50 VALUES( 'P0004A', 'ORTHOLINEAR', '1', '0', 3, 144.00, 23 );
51 INSERT INTO Pcb_T( Pcb_Id, Pcb_Layout, Led, Hotswap, Pcb_Pins, Pcb_Cost, Pcb_Quantity )
52 VALUES( 'P0005A', 'STANDARD 75', '0', '0', 5, 76.99, 17 );
53
54 --populate the Switches Table
55 INSERT INTO Switches_T( Switches_Id, Switches_Name, Type, Switches_Pins, Switches_Cost, Switches_Quantity )
56 VALUES( 'S0001A', 'ZEALIOS V2', 'TACTILE', 5, 0.75, 400 );
57 INSERT INTO Switches_T( Switches_Id, Switches_Name, Type, Switches_Pins, Switches_Cost, Switches_Quantity )
58 VALUES( 'S0002A', 'TEALIOS V1', 'LINEAR', 5, 0.60, 300 );
59 INSERT INTO Switches_T( Switches_Id, Switches_Name, Type, Switches_Pins, Switches_Cost, Switches_Quantity )
60 VALUES( 'S0003A', 'CHERRY MX BLUE', 'CLICKY', 3, 0.45, 400 );
61 INSERT INTO Switches_T( Switches_Id, Switches_Name, Type, Switches_Pins, Switches_Cost, Switches_Quantity )
62 VALUES( 'S0004A', 'HOLY PANDA', 'TACTILE', 3, 0.80, 240 );
63 INSERT INTO Switches_T( Switches_Id, Switches_Name, Type, Switches_Pins, Switches_Cost, Switches_Quantity )
64 VALUES( 'S0005A', 'OUTEMU SKY', 'TACTILE', 5, 0.55, 300 );
65
66 --populate the Keys Table
67 INSERT INTO Keys_T( Keys_Id, Keys_Layout, Keys_Name, Keys_Material, Keys_Cost, Keys_Quantity)
68 VALUES( 'K0001A', 'STANDARD 100', 'BLACK ON WHITE', 'ABS', 121.99, 36 );
69 INSERT INTO Keys_T( Keys_Id, Keys_Layout, Keys_Name, Keys_Material, Keys_Cost, Keys_Quantity)
70 VALUES( 'K0002A', 'STANDARD 75', 'BLACK ON WHITE', 'PBT', 110.00, 40 );
71 INSERT INTO Keys_T( Keys_Id, Keys_Layout, Keys_Name, Keys_Material, Keys_Cost, Keys_Quantity)
72 VALUES( 'K0003A', 'STANDARD 100', 'WHITE ON BLUE', 'PBT', 90.00, 60 );
73 INSERT INTO Keys_T( Keys_Id, Keys_Layout, Keys_Name, Keys_Material, Keys_Cost, Keys_Quantity)
74 VALUES( 'K0004A', 'STANDARD 100', 'GREYSCAPE', 'ABS', 110.00, 30 );
75 INSERT INTO Keys_T( Keys_Id, Keys_Layout, Keys_Name, Keys_Material, Keys_Cost, Keys_Quantity)
76 VALUES( 'K0005A', 'STANDARD 100', 'NOIR', 'PBT', 160.00, 50 );
77
78
79 --populate the Customer Table
80 INSERT INTO Customer_T( Cust_Id, Cust_Fname, Cust_Lname, Cust_Email, Cust_Address, Cust_PostCode)
81 VALUES( 'C00001', 'Michael', 'Scott', 'mscott@gmail.com', '1385 Woodroffe Ave', 'K2G1V8');
82 INSERT INTO Customer_T( Cust_Id, Cust_Fname, Cust_Lname, Cust_Email, Cust_Address, Cust_PostCode)
83 VALUES( 'C00002', 'Jim', 'Halpert', 'jhalpert@gmail.com', '1400 Main Ave', 'K2G1V4');
84 INSERT INTO Customer_T( Cust_Id, Cust_Fname, Cust_Lname, Cust_Email, Cust_Address, Cust_PostCode)
85 VALUES( 'C00003', 'Kelly', 'Kapoor', 'kkapoor@gmail.com', '1764 Crescent Ave', 'K3G2G4');
86 INSERT INTO Customer_T( Cust_Id, Cust_Fname, Cust_Lname, Cust_Email, Cust_Address, Cust_PostCode)
87 VALUES( 'C00004', 'Pamela', 'Beesly', 'pbeesly@gmail.com', '2345 Linder Ave', 'K5G3F5');
88 INSERT INTO Customer_T( Cust_Id, Cust_Fname, Cust_Lname, Cust_Email, Cust_Address, Cust_PostCode)
89 VALUES( 'C00005', 'dwight', 'schrute', 'beets4life@gmail.com', '436 Berg Ave', 'K2G2B6');
90
91
92 --populate the Inovice Table
93 INSERT INTO Invoice_T( Invoice_Number, Cust_Id, Invoice_Date )
94 VALUES( 'I00001', 'C00001', '2015-02-15' );
95 INSERT INTO Invoice_T( Invoice_Number, Cust_Id, Invoice_Date )
96 VALUES( 'I00002', 'C00002', '2016-04-25' );
97 INSERT INTO Invoice_T( Invoice_Number, Cust_Id, Invoice_Date )
98 VALUES( 'I00003', 'C00003', '2017-06-12' );
99 INSERT INTO Invoice_T( Invoice_Number, Cust_Id, Invoice_Date )
100 VALUES( 'I00004', 'C00004', '2018-07-08' );
101 INSERT INTO Invoice_T( Invoice_Number, Cust_Id, Invoice_Date )
102 VALUES( 'I00005', 'C00005', '2019-08-24' );
103
104
105 --populate the Invoice Line Table
106 INSERT INTO Invoice_Line_T( Invoice_Number, Invoice_Line, Case_Id, Pcb_Id, Switches_Id, Keys_Id, Hours )
107 VALUES( 'I00001', 1, 'C0001A', 'P0001A', 'S0003A', 'K0002A', 1 );
108 INSERT INTO Invoice_Line_T( Invoice_Number, Invoice_Line, Case_Id, Pcb_Id, Switches_Id, Keys_Id, Hours )
109 VALUES( 'I00002', 1, 'C0002A', 'P0002A', 'S0001A', 'K0001A', 2 );
110 INSERT INTO Invoice_Line_T( Invoice_Number, Invoice_Line, Case_Id, Pcb_Id, Switches_Id, Keys_Id, Hours )
111 VALUES( 'I00003', 1, 'C0003A', 'P0001A', 'S0005A', 'K0004A', 1 );
112 INSERT INTO Invoice_Line_T( Invoice_Number, Invoice_Line, Case_Id, Pcb_Id, Switches_Id, Keys_Id, Hours )
113 VALUES( 'I00004', 1, 'C0004A', 'P0005A', 'S0001A', 'K0003A', 1 );
114 INSERT INTO Invoice_Line_T( Invoice_Number, Invoice_Line, Case_Id, Pcb_Id, Switches_Id, Keys_Id, Hours )
115 VALUES( 'I00005', 1, 'C0005A', 'P0003A', 'S0002A', 'K0005A', 1 );
116
117 --SELECT with WHERE statments
118 SELECT * FROM Pcb_T
119 WHERE Pcb_Pins = 5;
120
121 SELECT * FROM Switches_T
122 WHERE Switches_Pins = 5;
123
124 SELECT * FROM Keys_T
125 WHERE Keys_Layout = 'STANDARD 100';
126
127
128 --Two joins statements with sub queries
129 --Quickly see a customers built keyboards
130 SELECT Invoice_T.Invoice_Number, Cust_Id, Case_id, Pcb_Id, Switches_Id, Keys_Id FROM Invoice_T LEFT JOIN Invoice_Line_T
131 ON Invoice_T.Invoice_Number = Invoice_Line_T.Invoice_Number;
132 --Determine which switch options are available for PCBs with only 3 pins slots
133 SELECT Pcb_Id, Switches_Id, Switches_Pins, Type FROM Pcb_T RIGHT JOIN Switches_T
134 ON Pcb_T.Pcb_Pins = Switches_T.Switches_Pins
135 WHERE Pcb_Pins = 3;
136
137 --UNION query
138 SELECT Keys_Material FROM Keys_T
139 UNION ALL
140 SELECT Case_Material FROM Case_T;
141
142 --testing the views created in DDL file
143 SELECT * FROM Order_V;
144 SELECT * FROM Threepins_V;
145
146 --getting metadata
147 SELECT column_name, data_type, character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE
148 TABLE_NAME = 'layout_t' OR
149 TABLE_NAME = 'case_t' OR
150 TABLE_NAME = 'pcb_t' OR
151 TABLE_NAME = 'switches_t' OR
152 TABLE_NAME = 'keys_t' OR
153 TABLE_NAME = 'customer_t' OR
154 TABLE_NAME = 'invoice_t' OR
155 TABLE_NAME = 'invoice_Line_t';
156
157 -- eof Assignment2-DML.sql