Professional blog

Assignment 2

CIS2002 Database Design and Implementation

Semester 3, 2020

Student id: 0061124521

SECTION A (Advanced Data Modelling)

List of Relations

1. Edge Cloud Computing (IS-A) Customer
2. Edge Cloud Computing (IS-A) OLTP
3. Edge Cloud Computing (IS-A) OLAP
4. Edge Cloud Computing (IS-A) OLAP (HAS-A) Database One
5. Edge Cloud Computing (IS-A) OLTP (HAS-A) Database One
6. Edge Cloud Computing (IS-A) OLTP (HAS-A) Customer
7. Edge Cloud Computing (IS-A) OLAP (HAS-A) Customer

Edge Cloud Computing (IS-A) Customer:

List of Attributes

1. Name (PK)
2. Address, E-Mail
3. Telephone
4. Processing Bandwidth
5. Type of Service
6. Duration of Use.
7. –Processing Specialty (FK)

Edge Cloud Computing (IS-A) OLTP

List of Attributes

1. Data Downloads
2. Data Uploads
3. Point of Sales
4. Social Media
5. Accounting

Edge Cloud Computing (IS-A) OLAP

List of Attributes

1. Image Processing
2. Business Intelligence
3. Forecasting
4. Predicting and Recognition

Edge Cloud Computing (IS-A) OLAP (HAS-A) Database One :

List of Attributes

1. Name
2. Make
3. Model
4. Year
5. MAC Address
6. Location
7. Site IP Address
8. Processing Specialty (FK)

Edge Cloud Computing (IS-A) OLTP (HAS-A) Database One:

List of Attributes

1. Name
2. Make
3. Model
4. Year
5. MAC Address
6. Location
7. Site IP Address
8. Processing Specialty (FK)

CREATE TABLE Database_One (

Name varchar(255),

Make varchar(255),

Model varchar(255),

Year int,

Mac Address int,

Location varchar(255),

Site IP Address int,

Processing Specialty varchar(255),

PRIMARY KEY (Name),

FOREIGN KEY (Processing Specialty)

);

SECTION B (Normalization)

Freight Office (Freight id # Branch name, address, opening hours, office contact, total cargo, receiver id#, destination id#)

item(item_id# ,Freight id#, category, package, weight, dimensions, ship-by date, receive-by date, sender_id#)

sender (sender id#, Item_id#, sender name, sender contact, contact address))

Receiver (receiver id#, Item_id#, receiver name, receiver contact, receiver address)

Destination (Destination id#, destination country code, destination state, destination city, distribution Centre, distribution contact)

Table names, primary key and foreign key after converting to 3NF Form-

Table Name	Primary Key	Foreign key
Freight Office	Freight_id	receiver id, destination id
item	Item_id	Freight_id, sender_id
sender	Sender_id	Item_id
Receiver	Receiver_id	Item_id
Destination	Destination id

SECTION C (Advanced SQL)

1. select books.title ,orderitems.quantity from books inner join orderitems on orderitems.isbn =books.isbn where orderitems.paideach-books.cost/books.cost>.30

Output Screenshot

2. select books.category,publisher.name from books inner join publisher on books.pubid=publisher.pubid where books.category=”COMPUTER” or books.category= “FAMILY

LIFE”

Output Screenshot

3. select category ,count(isbn) as counts from books group by category having counts> 1;

Output Screenshot

4. select title ,isbn,cost from books group by category having count(isbn)<2 order by title;

Output Screenshot

select category ,round( (retail-discount-cost)/count(isbn) )as AVERAGE_CATEGORY_PROFIT,STDEV( (retail-discount-cost)/count(isbn) ) as SDV_PROFIT from books group by category order by AVERAGE_CATEGORY_PROFIT ;

Output Screenshot

6. select retail,name,id,category from books join orderitems on orderitems.isbn =books.isbn group by category having count(quantity)>3

The post CIS2002 Database Design and Implementation appeared first on My Assignment Online.