University of Toronto
CSC343S, Fall 2014
Assignment 3
Due date revised from course syllabus: Mon 1 Dec, at 10:00 pm sharp!
IMPORTANT: Read the Piazza discussion board for any updates regarding this assignment. We
will provide a summary of key clarications
CSC 343H1S
Question 1.
Midterm Test (day) Solutions
Winter 2013
[7 marks]
Suppose we have these relations: M(a, b), N(c, d, e), and O(m, g) and these constraints:
1. N[c] M[a]
2. N[e] O[g]
Part (a) [3 marks]
Draw a valid instance of this schema, with thre
Yangsheng Liu
998178564
g2liuyan
0 grace day used
1 out of 3 grace day left so far
CSC343 Assignment 3
Part A: Assumptions
1. The email of PC Chairs has to be in the format of xxx@xxx.com/ca/.
2. The phone number of PC Chairs has to be in the format of xx
CSC 343
Introduction to Databases
Diane Horton
Winter 2015
Why study databases?
Interesting concepts and techniques.
Spans computer science, including OS, languages,
theory, AI, multimedia, logic.
Databases have become increasingly important
shift fro
Query Languages for XML
csc343, Introduction to Databases
Diane Horton
with material from Ryan Johnson, Manos Papagelis, Jeff Ullman, Ramona Truta,
and Rene Miller
Winter 2015
Data Model
We saw that an XML le has a tree structure.
White space in the le
University of Toronto
CSC343, Fall 2014
Relational Algebra Exercises
These are solutions to some of the exercises we worked on in class. The remaining solutions will be posted
once we have nished the exercises. Important: There are other good answers to e
CSC 343H1S 2013 Test 1
Duration 50 minutes
Aids allowed: none
Student Number:
Last Name:
First Name:
Lecture Section: Day
Instructor: Horton
Please ll out the identication section above as well as the one on the back page,
and read the instructions below.
CSC343 Fall 2014
Assignment 2
Interactive & Embedded SQL Queries
Due date:
Mon 10 Nov at 11:00pm
Instructions
1.
2.
3.
4.
Read this assignment thoroughly before you proceed. Failure to follow instructions can affect your grade.
Download the database schem
University of Toronto
csc343, Fall 2014
Assignment 1
Due: Thursday, 9 October, at 11:00 pm sharp!
About the domain
For this assignment, you will write queries and integrity constraints on a database for a museum.
The objects in a museum can be as small as
IN
PL
D
H
A
SE
N
EA
UNIVERSITY OF TORONTO
Faculty of Arts and Science
SE
N
D
IN
Duration 3 hours
EA
A
CSC 343 H1S
Instructor: Horton
PL
H
APRIL 2013S EXAMINATIONS
Examination Aids: None
Student Number:
Family Name(s):
Given Name(s):
Do not turn this page
CSC 343H1S 2010 Test 1
Duration 50 minutes
Aids allowed: none
Student Number:
Last Name:
First Name:
Lecture Section: Tuesday evening
Instructor: Horton
Do not turn this page until you have received the signal to start.
(Please ll out the identication sec
CSC 343H1S 2012 Midterm Test
Duration 50 minutes
Aids allowed: none
Student Number:
Last Name:
First Name:
Lecture Section: L0101 (daytime)
Instructor: Horton
Do not turn this page until you have received the signal to start.
(Please ll out the identicati
CSC 343H1F 2011 Midterm Test
Duration 50 minutes
Aids allowed: none
Student Number:
Last Name:
First Name:
Lecture Section: L0101 (Tuesday)
Instructor: Horton
Do not turn this page until you have received the signal to start.
(Please ll out the identicati
Midterm Test (L0101) Solutions
CSC 343H1F
Question 1.
Fall 2014
[6 marks]
Part (a) [2 marks]
Consider this schema:
A(orange, popcorn)
B(sugar, tea)
A[orange] B[sugar]
Suppose relation A has 17 tuples. Circle the one statement below that is the strongest t
University of Toronto
csc343, Winter 2015
Assignment 1 : Sample Solutions
Note that there are multiple correct answers to all of these questions.
1. Report the user name of every student who has never worked with anyone, but has indeed submitted
at least
University of Toronto
csc343, Winter 2015
Assignment 2
Due: Tuesday, March 10, at 8:00 pm sharp!
General Instructions
1. Please read this assignment thoroughly before you proceed. Failure to follow instructions can aect your
grade.
2. We strongly encourag
XQuery Query Language
"
l bank
xnl
.
"
1
11 Question
[
*
1 Hint
'
=
seriously
?
'
]
/ 1
Hint /
'
'
"
.
aggregation
Question Bank
/
$q
g
euro
:
for
4 count
$q
in
where
return
(
auestron
means
go up
)
q
deal
"
$q1*/Hnt
(
.
/ Text
:
variable
:
,
$ql*l
bank

XML & DTDs
csc343, Introduction to Databases
Diane Horton
originally based on slides by Jeff Ullman
Winter 2015
Introduction
The relational model is very rigid:
Everything must be a table.
The schema must be dened in advance.
Everything must conform t
University of Toronto
CSC343, Winter 2015
Relational Algebra Exercises
These are solutions to some of the exercises we worked on in class. The remaining solutions will be posted
once we have nished the exercises. Important: There are other good answers to
let $cp:= doc("property.xml")/PROPERTY[COMMERCIAL]
return
<COMMERCIAL_UNITS>
cfw_for $property in $cp
for $unit in $property/(UNITSINGLE_UNIT)
return <UNIT>
cfw_$unit/@*
cfw_$unit/*
cfw_$property/ADDRESS
</UNIT>
</COMMERCIAL_UNITS>
University of Toronto
CSC343, Winter 2015
SQL: Exercises with nulls
1. Suppose we have a table called Runnymede with the following content:
name
 age  grade
++diane


8
will


8
cate


1
tom


micah


1
jamieson 

2
(6 rows)
What is the o
Relational Algebra
csc343, Introduction to Databases
Diane Horton
Winter 2015
RA Basics
(covered by your week 2 Prep)
Elementary Algebra
You did algebra in high school
27y2 + 8y  3
Operands:
Operators:
3
Relational Algebra
Operands: tables
Operator
The Relational Model
csc343, Introduction to Databases
Diane Horton
Fall 2014
Recap
The relational model is based on the concept of a
relation or table.
Two example relations:
Teams Name
Rangers
Ducks
Choppers
Home Field
Runnymede CI
Humber Public
High
SQL:
Data Denition Language
csc343, Introduction to Databases
Diane Horton
Winter 2015
Types
Table attributes have types
When creating a table, you must dene the type of
each attribute.
Analogous to declaring a variables type in a
program. Eg, int num;
Views
The idea
A view is a relation dened in terms of stored
tables (called base tables) and other views.
Access a view like any base table.
Two kinds of view:
Virtual: no tuples are stored; view is just a query
for constructing the relation when need
SQL:
Data Manipulation Language
csc343, Introduction to Databases
Diane Horton
Winter 2015
Introduction
So far, we have dened database schemas and queries
mathematically.
SQL is a formal language for doing so with a DBMS.
Structured Query Language, but
University of Toronto
CSC343, Winter 2015
Relational Algebra Exercises
Schema
Note: breadth is a boolean indicating whether or not a course satises the breadth requirement for degrees
in the Faculty of Arts and Science.
Student(sID, surName, rstName, camp
SQL:
Data Manipulation Language
csc343, Introduction to Databases
Diane Horton
Winter 2015
Introduction
So far, we have dened database schemas and queries
mathematically.
SQL is a formal language for doing so with a DBMS.
Structured Query Language, but