• +1-617-874-1011 (US)
  • +44-117-230-1145 (UK)
Live Chat
Follow Us:

Relational schema and MySQL database

1) Relational Schema: -

Relation

Attribute

Data type

length

Key constraints

Referencing table (column)

Null/ not null

location

locationId

INT

-

PK

-

NOT NULL, UNIQUE

streetNo

INT

-

-

-

NOT NULL

streetName

VARCHAR

20

-

-

NOT NULL

suburb

VARCHAR

20

-

-

NOT NULL

stateName

VARCHAR

20

-

-

NOT NULL

postCode

INT

-

-

-

NOT NULL

stores

storeNo

VARCHAR

10

PK

-

NOT NULL, UNIQUE

storeName

VARCHAR

25

-

-

NOT NULL

mobile

VARCHAR

12

-

-

NOT NULL

email

VARCHAR

30

-

-

NOT NULL

faxNo

VARCHAR

10

-

-

NULL

locationId

INT

-

FK

location (locationNo)

NOT NULL

departments

departmentNo

VARCHAR

10

PK

-

NOT NULL, UNIQUE

title

VARCHAR

20

-

-

NOT NULL

mobile

VARCHAR

12

-

-

NOT NULL

email

VARCHAR

30

-

-

NOT NULL

storeDepartment

storeNo

VARCHAR

10

PK, FK

store(storeNo)

NOT NULL

departmentNo

VARCHAR

10

PK, FK

department (departmentNo)

NOT NULL

employee

employeeNo

VARCHAR

10

PK

-

NOT NULL, UNIQUE

storeNo

VARCHAR

10

FK

storeDepartment (storeNo)

NOT NULL

departmentNo

VARCHAR

10

FK

storeDepartment (departmentNo)

NOT NULL

firstName

VARCHAR

20

-

-

NOT NULL

lastName

VARCHAR

20

-

-

NOT NULL

address

VARCHAR

50

-

-

NOT NULL

mobile

VARCHAR

12

-

-

NOT NULL

email

VARCHAR

30

-

-

NOT NULL

tfn

VARCHAR

10

-

-

NULL

joinDate

DATE

-

-

-

NOT NULL

workRole

VARCHAR

10

-

-

NOT NULL

fullTime

employeeNo

VARCHAR

10

PK, FK

employee (employeeNo)

NOT NULL, UNIQUE

annualSalary

DOUBLE

(10,2)

-

-

NOT NULL

casual

employeeNo

VARCHAR

10

PK, FK

employee (employeeNo)

NOT NULL

hourlyRate

DOUBLE

(10, 2)

-

-

NOT NULL

payslips

payslipNo

VARCHAR

10

PK

-

NOT NULL, UNIQUE

totalHours

DOUBLE

(10, 2)

-

-

NOT NULL

grossPay

DOUBLE

(10, 2)

-

-

NOT NULL

employeeNo

VARCHAR

10

FK

employee (employeeNo)

NOT NULL

product

productNo

VARCHAR

10

PK

-

NOT NULL, UNIQUE

productName

VARCHAR

20

-

-

NOT NULL

productType

VARCHAR

20

-

-

NOT NULL

brand

VARCHAR

30

-

-

NOT NULL

price

DOUBLE

(10, 2)

-

-

NOT NULL

description

VARCHAR

200

-

-

NULL

suppliers

supplierNo

VARCHAR

10

PK

-

NOT NULL, UNIQUE

supplierName

VARCHAR

30

-

-

NOT NULL

address

VARCHAR

50

-

-

NOT NULL

mobile

VARCHAR

12

-

-

NOT NULL

email

VARCHAR

30

-

-

NOT NULL

inventory

productNo

VARCHAR

10

PK, FK

product(productNo)

NOT NULL

stotreNo

VARCHAR

10

PK, FK

store(storeNo)

NOT NULL

quantity

DOUBLE

(10, 2)

-

-

NOT NULL

supply

supplyNo

INT

-

PK

-

NOT NULL, UNIQUE

storeNo

VARCHAR

10

FK

inventory (storeNo)

NOT NULL

productNo

VARCHAR

10

FK

inventory (productNo)

NOT NULL

supplierNo

VARCHAR

10

FK

supplier (supplierNo)

NOT NULL

quantity

INT

-

-

-

NOT NULL

supplyDate

DATE

-

-

-

NOT NULL

customers

customerNo

VARCHAR

10

PK

-

NOT NULL, UNIQUE

firstName

VARCHAR

15

-

-

NOT NULL

lastName

VARCHAR

15

-

-

NOT NULL

mobile

VARCHAR

12

-

-

NOT NULL

address

VARCHAR

50

-

-

NOT NULL

orders

orderNo

VARCHAR

10

PK

-

NOT NULL, UNIQUE

customerNo

VARCHAR

10

FK

customer (customerNo)

NOT NULL

orderDate

DATE

-

-

-

NOT NULL

orderItems

orderNo

VARCHAR

10

PK, FK

orders(orderNo)

NOT NULL

productNo

VARCHAR

10

PK, FK

product (productNo)

NOT NULL

quantity

INT

-

-

-

NOT NULL

relation schema
relation schema
relation schema

2) MYSQL database: -

· Database schema: -

Figure 2: database schema

· referential integrity constraints: -

Figure 3: casual

Figure 4: customers

Figure 5: departments

Figure 6:employee

Figure 7:fullTime

Figure 8: inventory

Figure 9: location

Figure 10: orderItems

Figure 11: orders

Figure 12: paySlips

Figure 13: product

Figure 14: supply

Figure 15: stores

Figure 16: storeDepartment

Figure 17: supplier

· structure of each table: -

Describe orderItems;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| orderNo | varchar(10) | NO | PRI | NULL | |

| productNo | varchar(10) | NO | PRI | NULL | |

| quantity | int(11) | NO | | NULL | |

+-----------+-------------+------+-----+---------+-------+

3 rows in set (0.03 sec)

Describe orders;

+------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| orderNo | varchar(10) | NO | PRI | NULL | |

| orderDate | date | NO | | NULL | |

| customerNo | varchar(10) | NO | MUL | NULL | |

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

Describe customers;

+------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| customerNo | varchar(10) | NO | PRI | NULL | |

| firstName | varchar(15) | NO | | NULL | |

| lastName | varchar(15) | NO | | NULL | |

| mobile | varchar(12) | NO | | NULL | |

| address | varchar(50) | NO | | NULL | |

+------------+-------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

Describe supply;

+------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| supplyNo | int(11) | NO | PRI | NULL | |

| storeNo | varchar(10) | NO | | NULL | |

| productNo | varchar(10) | NO | MUL | NULL | |

| supplierNo | varchar(10) | NO | MUL | NULL | |

| quantity | int(11) | NO | | NULL | |

| supplyDate | date | NO | | NULL | |

+------------+-------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

Describe inventory;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| productNo | varchar(10) | NO | PRI | NULL | |

| storeNo | varchar(10) | NO | PRI | NULL | |

| quantity | int(11) | NO | | NULL | |

+-----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

Describe suppliers;

+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+-------+

| supplierNo | varchar(10) | NO | PRI | NULL | |

| supplierName | varchar(30) | NO | | NULL | |

| address | varchar(50) | NO | | NULL | |

| mobile | varchar(12) | NO | | NULL | |

| email | varchar(30) | NO | | NULL | |

+--------------+-------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

Describe product;

+-------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------+--------------+------+-----+---------+-------+

| productNo | varchar(10) | NO | PRI | NULL | |

| productName | varchar(20) | NO | | NULL | |

| productType | varchar(20) | NO | | NULL | |

| brand | varchar(30) | NO | | NULL | |

| price | double(10,2) | NO | | NULL | |

| description | varchar(200) | YES | | NULL | |

+-------------+--------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

Describe paySlips;

+------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+--------------+------+-----+---------+-------+

| payslipNo | varchar(10) | NO | PRI | NULL | |

| totalHours | double(10,2) | NO | | NULL | |

| grossPay | double(10,2) | NO | | NULL | |

| employeeNo | varchar(10) | NO | MUL | NULL | |

+------------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

Describe casual;

+------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+--------------+------+-----+---------+-------+

| employeeNo | varchar(10) | NO | PRI | NULL | |

| hourlyRate | double(10,2) | NO | | NULL | |

+------------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

Describe fullTime;

+--------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+--------------+------+-----+---------+-------+

| employeeNo | varchar(10) | NO | PRI | NULL | |

| annualSalary | double(10,2) | NO | | NULL | |

+--------------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

Describe employee;

+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+-------+

| employeeNo | varchar(10) | NO | PRI | NULL | |

| storeNo | varchar(10) | NO | MUL | NULL | |

| departmentNo | varchar(10) | NO | | NULL | |

| firstName | varchar(20) | NO | | NULL | |

| lastName | varchar(20) | NO | | NULL | |

| address | varchar(50) | NO | | NULL | |

| mobile | varchar(12) | NO | | NULL | |

| email | varchar(30) | NO | | NULL | |

| tfn | varchar(10) | YES | | NULL | |

| joinDate | date | NO | | NULL | |

| workRole | varchar(10) | NO | | NULL | |

+--------------+-------------+------+-----+---------+-------+

11 rows in set (0.00 sec)

Describe storeDepartment;

+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+-------+

| storeNo | varchar(10) | NO | PRI | NULL | |

| departmentNo | varchar(10) | NO | PRI | NULL | |

+--------------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

Describe departments;

+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+-------+

| departmentNo | varchar(10) | NO | PRI | NULL | |

| title | varchar(20) | NO | | NULL | |

| mobile | varchar(12) | NO | | NULL | |

| email | varchar(30) | NO | | NULL | |

+--------------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

Describe stores;

+------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| storeNo | varchar(10) | NO | PRI | NULL | |

| storesName | varchar(25) | NO | | NULL | |

| mobile | varchar(12) | NO | | NULL | |

| email | varchar(30) | NO | | NULL | |

| faxNo | varchar(10) | YES | | NULL | |

| locationId | int(11) | NO | MUL | NULL | |

+------------+-------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

Describe location;

+------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| locationId | int(11) | NO | PRI | NULL | |

| streetNo | int(11) | NO | | NULL | |

| streetName | varchar(20) | NO | | NULL | |

| suburb | varchar(20) | NO | | NULL | |

| stateName | varchar(20) | NO | | NULL | |

| postCode | int(11) | NO | | NULL | |

+------------+-------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

3) Insert data into tables: -

SELECT *FROM orderItems;

+---------+-----------+----------+

| orderNo | productNo | quantity |

+---------+-----------+----------+

| 1005 | 1 | 3 |

| 1005 | 2 | 4 |

| 1005 | 3 | 2 |

| 2 | 1 | 4 |

| 2 | 2 | 4 |

| 2 | 3 | 2 |

| 5 | 1 | 3 |

| 5 | 2 | 1 |

| 5 | 3 | 3 |

+---------+-----------+----------+

9 rows in set (0.00 sec)

SELECT *FROM orders;

+---------+------------+------------+

| orderNo | orderDate | customerNo |

+---------+------------+------------+

| 1005 | 2017-07-03 | 1 |

| 2 | 2017-08-04 | 1 |

| 5 | 2017-09-05 | 1 |

| 6 | 2017-10-06 | 2 |

+---------+------------+------------+

4 rows in set (0.00 sec)

SELECT *FROM customers;

+------------+-----------+----------+------------+---------------+

| customerNo | firstName | lastName | mobile | address |

+------------+-----------+----------+------------+---------------+

| 1 | nji | Caan | 6787634521 | nji@gmail.com |

| 2 | mni | Caan | 6787634522 | mni@gmail.com |

| 3 | fgi | Caan | 6787634523 | fgi@gmail.com |

+------------+-----------+----------+------------+---------------+

3 rows in set (0.00 sec)

SELECT *FROM supply;

+----------+---------+-----------+------------+----------+------------+

| supplyNo | storeNo | productNo | supplierNo | quantity | supplyDate |

+----------+---------+-----------+------------+----------+------------+

| 1 | 1 | 1 | 1 | 3 | 2013-01-01 |

| 2 | 1 | 2 | 2 | 2 | 2013-01-02 |

| 3 | 2 | 3 | 3 | 4 | 2013-01-03 |

| 4 | 2 | 1 | 1 | 1 | 2013-01-04 |

+----------+---------+-----------+------------+----------+------------+

4 rows in set (0.03 sec)

SELECT *FROM inventory;

+-----------+---------+----------+

| productNo | storeNo | quantity |

+-----------+---------+----------+

| 1 | 1 | 10 |

| 1 | 2 | 11 |

| 1 | 3 | 10 |

| 2 | 1 | 11 |

| 2 | 3 | 15 |

| 3 | 1 | 13 |

| 3 | 2 | 16 |

+-----------+---------+----------+

7 rows in set (0.04 sec)

SELECT *FROM suppliers;

+------------+--------------+---------+------------+----------------+

| supplierNo | supplierName | address | mobile | email |

+------------+--------------+---------+------------+----------------+

| 1 | bhi | US | 9890987876 | bhi@gmail.com |

| 2 | nki | US | 9890987877 | nki@gmail.com |

| 3 | miko | US | 9890987816 | miko@gmail.com |

+------------+--------------+---------+------------+----------------+

3 rows in set (0.10 sec)

SELECT *FROM product;

+-----------+-------------+-------------+-------+--------+-------------+

| productNo | productName | productType | brand | price | description |

+-----------+-------------+-------------+-------+--------+-------------+

| 1 | nini | fashion | dfg | 456.00 | NULL |

| 2 | jkmo | fashion | dfc | 567.00 | NULL |

| 3 | nmkl | fashion | ertg | 56.00 | NULL |

| 4 | bhnj | fashion | fgv | 76.00 | NULL |

| 5 | yuhi | fashion | rfghy | 456.00 | NULL |

+-----------+-------------+-------------+-------+--------+-------------+

5 rows in set (0.12 sec)

SELECT *FROM paySlips;

+-----------+------------+----------+------------+

| payslipNo | totalHours | grossPay | employeeNo |

+-----------+------------+----------+------------+

| 1 | 45.90 | 744.09 | 1 |

| 2 | 45.87 | 345.67 | 2 |

| 3 | 87.09 | 567.09 | 3 |

+-----------+------------+----------+------------+

3 rows in set (0.04 sec)

SELECT *FROM casual;

+------------+------------+

| employeeNo | hourlyRate |

+------------+------------+

| 10 | 300.00 |

| 11 | 500.00 |

| 12 | 200.00 |

| 7 | 600.00 |

| 8 | 500.00 |

| 9 | 200.00 |

+------------+------------+

6 rows in set (0.02 sec)

SELECT *FROM fullTime;

+------------+--------------+

| employeeNo | annualSalary |

+------------+--------------+

| 1 | 56789678.00 |

| 2 | 56789876.00 |

| 3 | 56789987.00 |

| 4 | 78987688.00 |

| 5 | 56784567.00 |

| 6 | 34567569.00 |

+------------+--------------+

6 rows in set (0.12 sec)

SELECT *FROM employee;

+------------+---------+--------------+-----------+----------+---------+------------+-----------------+---------+------------+------------+

| employeeNo | storeNo | departmentNo | firstName | lastName | address | mobile | email | tfn | joinDate | workRole |

+------------+---------+--------------+-----------+----------+---------+------------+-----------------+---------+------------+------------+

| 1 | 1 | 1 | dfio | Caan | US | 1234323430 | dfio@gmail.com | fd45 | 2015-01-01 | supervisor |

| 10 | 3 | 2 | ert | Caan | US | 1234323439 | ert@gmail.com | t566 | 2015-01-10 | employee |

| 11 | 3 | 3 | rty | Caan | US | 1234323134 | rty@gmail.com | g56 | 2015-01-11 | employee |

| 12 | 3 | 4 | rtyu | Caan | US | 1234322434 | rtyu@gmail.com | 6g5 | 2015-01-12 | employee |

| 2 | 1 | 2 | huio | Caan | US | 1234323431 | huio@gmail.com | dr56 | 2015-01-02 | supervisor |

| 3 | 1 | 3 | rtyu | Caan | US | 1234323432 | rtyu@gmail.com | gh67 | 2015-01-03 | supervisor |

| 4 | 1 | 4 | fghj | Caan | US | 1234323433 | fgghj@gmail.com | gtfr456 | 2015-01-04 | supervisor |

| 5 | 2 | 1 | dfg | Caan | US | 1234323434 | dfg@gmail.com | dgt64 | 2015-01-05 | manager |

| 6 | 2 | 2 | sdf | Caan | US | 1234323435 | sdf5@gmail.com | grr4556 | 2015-01-06 | manager |

| 7 | 2 | 3 | asd | Caan | US | 1234323436 | asd@gmail.com | g355 | 2015-01-07 | manager |

| 8 | 2 | 4 | qwer | Caan | US | 1234323437 | qwer@gmail.com | gr434 | 2015-01-08 | manager |

| 9 | 3 | 1 | wer | Caan | US | 1234323438 | wer@gmail.com | tr45 | 2015-01-09 | employee |

+------------+---------+--------------+-----------+----------+---------+------------+-----------------+---------+------------+------------+

12 rows in set (0.01 sec)

SELECT *FROM storeDepartment;

+---------+--------------+

| storeNo | departmentNo |

+---------+--------------+

| 1 | 1 |

| 2 | 1 |

| 3 | 1 |

| 1 | 2 |

| 2 | 2 |

| 3 | 2 |

| 1 | 3 |

| 2 | 3 |

| 3 | 3 |

| 1 | 4 |

| 2 | 4 |

| 3 | 4 |

+---------+--------------+

12 rows in set (0.04 sec)

SELECT *FROM departments;

+--------------+----------+------------+--------------------+

| departmentNo | title | mobile | email |

+--------------+----------+------------+--------------------+

| 1 | HR | 5434543453 | HR789hh@gmail.com |

| 2 | accounts | 5434543455 | ACCtgw@gmail.com |

| 3 | finance | 5434543456 | FM234gju@gmail.com |

| 4 | supply | 5434543457 | SM2rte@gmail.com |

+--------------+----------+------------+--------------------+

4 rows in set (0.13 sec)

SELECT *FROM stores;

+---------+-------------+------------+------------------+-------+------------+

| storeNo | storesName | mobile | email | faxNo | locationId |

+---------+-------------+------------+------------------+-------+------------+

| 1 | hiyna koko | 1234567890 | hiyna@gmail.com | er45 | 1 |

| 2 | jihono jiji | 1234567891 | jihono@gmail.com | fgv4 | 2 |

| 3 | kio hinop | 1234567892 | kio@gmail.com | gfv56 | 3 |

+---------+-------------+------------+------------------+-------+------------+

3 rows in set (0.11 sec)

SELECT *FROM location;

+------------+----------+---------------+--------+-----------+----------+

| locationId | streetNo | streetName | suburb | stateName | postCode |

+------------+----------+---------------+--------+-----------+----------+

| 1 | 6759 | MKLAS | HJNK | LM | 1234 |

| 2 | 7898 | George | JKML | NM | 1232 |

| 3 | 6709 | George IJKIPO | KML | ML | 6787 |

+------------+----------+---------------+--------+-----------+----------+

3 rows in set (0.03 sec)

Part D: SQL: -

Question 1:

SQL:

SELECT CONCAT(firstName,' ',lastName) AS "customers full name", mobile, address

FROM customers

ORDER BY customerNo;

Output:

+---------------------+------------+---------------+

| customers full name | mobile | address |

+---------------------+------------+---------------+

| nji Caan | 6787634521 | nji@gmail.com |

| mni Caan | 6787634522 | mni@gmail.com |

| fgi Caan | 6787634523 | fgi@gmail.com |

+---------------------+------------+---------------+

3 rows in set (0.00 sec)

Question 2:

SQL:

SELECT *FROM product

WHERE price<100;

Output:

+-----------+-------------+-------------+-------+-------+-------------+

| productNo | productName | productType | brand | price | description |

+-----------+-------------+-------------+-------+-------+-------------+

| 3 | nmkl | fashion | ertg | 56.00 | NULL |

| 4 | bhnj | fashion | fgv | 76.00 | NULL |

+-----------+-------------+-------------+-------+-------+-------------+

2 rows in set (0.00 sec)

Question 3:

SQL:

SELECT product.productNo, product.brand as title, orderItems.quantity,

product.price, (product.price*orderItems.quantity) AS "total amount"

FROM product INNER JOIN orderItems ON product.productNo=orderItems.productNo

INNER JOIN orders ON orders.orderNo=orderItems.orderNo

WHERE orders.orderNo='1005';

Output:

+-----------+-------+----------+--------+--------------+

| productNo | title | quantity | price | total amount |

+-----------+-------+----------+--------+--------------+

| 1 | dfg | 3 | 456.00 | 1368.00 |

| 2 | dfc | 4 | 567.00 | 2268.00 |

| 3 | ertg | 2 | 56.00 | 112.00 |

+-----------+-------+----------+--------+--------------+

3 rows in set (0.00 sec)

Question 4:

SQL:

SELECT stores.storesName,

CONCAT(location.streetNo,' ',location.streetName,' ',location.suburb,

' ',location.statename,' ',location.postcode) as "Full address",

stores.faxNo

FROM stores INNER JOIN location ON stores.locationId=location.locationId

WHERE LOWER(location.streetName) LIKE'%george%';

Output:

+-------------+--------------------------------+-------+

| storesName | Full address | faxNo |

+-------------+--------------------------------+-------+

| jihono jiji | 7898 George JKML NM 1232 | fgv4 |

| kio hinop | 6709 George IJKIPO KML ML 6787 | gfv56 |

+-------------+--------------------------------+-------+

2 rows in set (0.00 sec)

Question 5:

SQL:

SELECT *FROM orders WHERE orderNo IN

(SELECT orderNo FROM (SELECT orderNo, COUNT(productNo) FROM orderItems

GROUP BY orderNo

HAVING COUNT(productNo)>1) A);

Output:

+---------+------------+------------+

| orderNo | orderDate | customerNo |

+---------+------------+------------+

| 1005 | 2017-07-03 | 1 |

| 2 | 2017-08-04 | 1 |

| 5 | 2017-09-05 | 1 |

+---------+------------+------------+

3 rows in set (0.00 sec)

Question 6:

SQL:

SELECT customerNo, CONCAT(firstName,' ',lastName) AS customersName, address

FROM customers

WHERE customerNo NOT IN (SELECT customerNo FROM orders);

Output:

+------------+---------------+---------------+

| customerNo | customersName | address |

+------------+---------------+---------------+

| 3 | fgi Caan | fgi@gmail.com |

+------------+---------------+---------------+

1 row in set (0.03 sec)

Question 7:

SQL:

SELECT employee.employeeNo, employee.firstName,employee.lastName,

departments.title as deptName, stores.storesName

FROM employee, departments, stores, storedepartment

WHERE employee.storeNo=storedepartment.storeNo

AND employee.departmentNo=storedepartment.departmentNo

AND stores.storeNo=storedepartment.storeNo

AND departments.departmentNo=storedepartment.departmentNo;

Output:

+------------+-----------+----------+----------+-------------+

| employeeNo | firstName | lastName | deptName | storesName |

+------------+-----------+----------+----------+-------------+

| 1 | dfio | Caan | HR | hiyna koko |

| 10 | ert | Caan | accounts | kio hinop |

| 11 | rty | Caan | finance | kio hinop |

| 12 | rtyu | Caan | supply | kio hinop |

| 2 | huio | Caan | accounts | hiyna koko |

| 3 | rtyu | Caan | finance | hiyna koko |

| 4 | fghj | Caan | supply | hiyna koko |

| 5 | dfg | Caan | HR | jihono jiji |

| 6 | sdf | Caan | accounts | jihono jiji |

| 7 | asd | Caan | finance | jihono jiji |

| 8 | qwer | Caan | supply | jihono jiji |

| 9 | wer | Caan | HR | kio hinop |

+------------+-----------+----------+----------+-------------+

12 rows in set (0.09 sec)

Question 8:

SQL:

SELECT stores.storesName, COUNT(employee.employeeNo) AS "number of employees"

FROM employee, departments, stores, storedepartment

WHERE employee.storeNo=storedepartment.storeNo

AND employee.departmentNo=storedepartment.departmentNo

AND stores.storeNo=storedepartment.storeNo

AND departments.departmentNo=storedepartment.departmentNo

AND departments.title LIKE '%account%'

GROUP BY stores.storesName;

Output:

+-------------+---------------------+

| storesName | number of employees |

+-------------+---------------------+

| hiyna koko | 1 |

| jihono jiji | 1 |

| kio hinop | 1 |

+-------------+---------------------+

3 rows in set (0.03 sec)

Question 9:

SQL:

SELECT *FROM orders

WHERE orderDate BETWEEN '2017-06-03' AND '2018-07-01';

Output:

+---------+------------+------------+

| orderNo | orderDate | customerNo |

+---------+------------+------------+

| 1005 | 2017-07-03 | 1 |

| 2 | 2017-08-04 | 1 |

| 5 | 2017-09-05 | 1 |

| 6 | 2017-10-06 | 2 |

+---------+------------+------------+

4 rows in set (0.00 sec)

Question 10:

SQL:

SELECT customers.customerNo, COUNT(orders.orderNo) AS "total orders"

FROM customers LEFT JOIN orders ON customers.customerNo=orders.customerNo

GROUP BY customers.customerNo;

Output:

+------------+--------------+

| customerNo | total orders |

+------------+--------------+

| 1 | 3 |

| 2 | 1 |

| 3 | 0 |

+------------+--------------+

3 rows in set (0.00 sec)

Question 11:

SQL:

SELECT orders.orderNo, orders.orderDate, SUM(quantity) AS "total number of products"

FROM orders, orderItems

WHERE orders.orderNo=orderItems.orderNo

GROUP BY orders.orderNo, orders.orderDate

ORDER BY orders.orderNo DESC;

Output:

+---------+------------+--------------------------+

| orderNo | orderDate | total number of products |

+---------+------------+--------------------------+

| 5 | 2017-09-05 | 7 |

| 2 | 2017-08-04 | 10 |

| 1005 | 2017-07-03 | 9 |

+---------+------------+--------------------------+

3 rows in set (0.00 sec)

Part E: Personal Report –

While working on this assignment, we learnt a lot of new things. The first phase of the assignment was requirement gathering and analysis. We went through all the requirements provided to us in the requirements file and then analysed these requirements. After analysing the requirements, we prepared our Entity Relationship Diagram (ERD). We had made sure that the ERD prepared by us is covering all the requirements and it is prepared as per the requirements file only.

While working on the assignment, we faced several issues. We were stuck while preparing the ERD but after spending some time on the analysis part, we were finally able to complete the ERD. Also, we faced issues while identifying the primary keys in some of the tables. So, we have added surrogate keys in some of the tables as per our understanding. We also faced some issues while working on the queries and we couldn’t get them right in the first go. But, after spending some time and going through various online resources, we were finally able to get the correct result for all the queries.

Our database design is fully normalised and extendable. Any new requirements which may come in the future can easily be integrated into our database.

Keywords: MYSQL Tutorials, Data Relations homework help, SQL homework help, SQL project Help, Oracle Homework Help, XML homework help, XML and Relational Algebra assignment help, SQL Queries and Updates homework assignment help, XML Queries and Transformations tutoring help, Relational Database Design concept, Higher-Level Design help in UML and ERD, Data Warehousing and Data Mining, help with SQL 2008 database, support for Database help, Microsoft access and mysql database programmer, database development and xml database support, homework helper for asp database and uml modeling, uml sequence diagram and uml class diagram, database design development and software SQL server 2008.