Relational Algebra uses a specific syntax. The syntax varies from author to author, the operationsremain the same. In this assignment you are expected to use the exact syntax shown below.

You must type your answers in Microsoft word
Upload your submission on D2L
Using the tables presented in the assignment
Using the syntax presented here
Relational Algebra uses a specific syntax. The syntax varies from author to author, the operations
remain the same. In this assignment you are expected to use the exact syntax shown below.
SELECT – unary -allows us to choose a subset of rows from the original table and put them into a
new table. One table in – one table out. Example of syntax you are expected to use:
PROJECT – unary -choose a subset of columns and put in new table. Note: duplicate rows will be
removed; one table in – one table out. Example of syntax you are expected to use:
Copyright © 2023 by Dr. Joe Ganczarski, Brampton, Ontario
Al rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic,
electronic or mechanical – without the prior written permission of the author.
JOIN – binary – combine two tables on a common attribute and put in new table. Two tables in – one
table out. Example of syntax you are expected to use:
DIVIDE – binary. Two tables in – one table out. Example of syntax you are expected to use:
Result := DIVIDE Completed BY DBProject
Copyright © 2023 by Dr. Joe Ganczarski, Brampton, Ontario
Al rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic,
electronic or mechanical – without the prior written permission of the author.
UNION – binary. Two tables in – one table out.
Nomenclature convention for relations:
Tablename(fieldname1, fieldname2, …)
primary key has a single underline
foreign key – bold italics
concatenated primary key – both fields underlined (the comma and space are not underlined)
Copyright © 2023 by Dr. Joe Ganczarski, Brampton, Ontario
Al rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic,
electronic or mechanical – without the prior written permission of the author.
ASSIGNMENT- Part 1:
The following relations are part of a general store
database:
customer(custnum, custname, city, creditlimit)
orders(ordernum, orderdate, filled, custnum)
orderline(ordernum, productnum, quantity, salesprice)
product(productnum, descr, producttype, msrp, onhand)
Here is a sample data (assume there are more records)
Copyright © 2023 by Dr. Joe Ganczarski, Brampton, Ontario
Al rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic,
electronic or mechanical – without the prior written permission of the author.
Using PROJECT, SELECT and JOIN, write the
sequence of operations to answer each of the
following questions:
1a. What are the names and customer numbers of
customers who are from Mississauga? (2.5 pts)
1b.What are the names of products (descry) and
product types of products purchased by customers
from Brampton? (2.5 pts)
Copyright © 2023 by Dr. Joe Ganczarski, Brampton, Ontario
Al rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic,
electronic or mechanical – without the prior written permission of the author.
COSC 2307 Assignment 1 – Part 2
SQL
Guideline:
You must type your answers in Microsoft word and if required also include sql file.
Upload your submission on D2L
Use the tables presented in the assignment
Nomenclature convention for relations:
Tablename(fieldname1, fieldname2, …)
primary key has a single underline
just the foreign key – bold italics
concatenated primary key – both fields underlined (the comma and space are not underlined)
Note: intersection tables will have foreign keys. They might have concatenated primary key – but
they will not be shown as bold italics.
ASSIGNMENT – Part 2:
Question 2.a
Create a script which will create the tables listed below. Please
prefix table names with your email userID. For example, if
your userID is pukhraj, pukhraj_customer should be the table
name. Make sure to use EXACT table/field names as in the
assignment. Changing table/field names is not allowed, so you
could get 0 points for the assignment!!!
customer(custnum, custname, city, creditlimit)
orders(ordernum, orderdate, filled, custnum)
orderline(ordernum, productnum, quantity, salesprice)
product(productnum, descr, producttype, msrp, onhand)
Copyright © 2023 by Dr. Joe Ganczarski, Brampton, Ontario
Al rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic,
electronic or mechanical – without the prior written permission of the author.
Please note: in orderline(ordernum, productnum) table:
You have one concatenated primary key (ordernum, productnum) and two
foreign keys: ordernum – primary key is ordernum in the orders table, and second
foreign key: productnum – primary key is productnum in the product table.
Start the script with a series of DROP statements so that as you correct mistakes
you will start fresh each time. To avoid referential integrity errors, the table
drops should be in the opposite order of the create table statements.
Put script name and your name in the script heading –see example:
Example of a script for MySQL:
/script1.sql Student Name: ——/
USE sh;
DROP TABLE IF EXISTS part;
DROP TABLE IF EXISTS salesrep;
CREATE TABLE part
(Partno CHAR(4) PRIMARY KEY,
Partdesc VARCHAR(20),
Onhand INTEGER,
Partclass CHAR(2) check (Partclass IN (‘AP’,’HW’,’KI’,’SP’)),
Unitprice DECIMAL(6,2)
);
CREATE TABLE salesrep
(Srepno CHAR(3),
Srepname VARCHAR(25),
Srepstreet VARCHAR(30),
Srepcity VARCHAR(15) NOT NULL,
Srepprov VARCHAR(3) NOT NULL,
Sreppcode VARCHAR(6) NOT NULL,
Copyright © 2023 by Dr. Joe Ganczarski, Brampton, Ontario
Al rights reserved. No part of this work may be reproduced or used in any form or by any means – graphic,
electronic or mechanical – without the prior written permission of the author.
Totcomm DECIMAL(8,2),
Commrate DECIMAL(3,2),
CONSTRAINT pkslsrep PRIMARY KEY (Srepno)
);
Test your script and if it is working.
Submit in one sql file for grading.
Question 2.b
Write SQL script to insert sample data to the
tables you just created in question 1 of the
assignment. Use your name for one of the
customer record. Submit also sql script.
Total: 5 points