CPSC 203 Study Guide - Final Guide: Data Manipulation Language, Unique Key, Aggregate Function

185 views26 pages
1 Dec 2013
Course
Professor

For unlimited access to Study Guides, a Grade+ subscription is required.

Database & Data Modeling
Databases
Organized collection of data
Minimizes redundancy
oWastes space and produce anomalies
Makes it easier to access and modify data
Examples: University and bank records
Typically is a collection of tables
Example Database
Page 92
Relations
Table and Relation means the same thing
Ex: In the Project we have four difference tuples. Project = {(1, Web Shopping,
Calgary, 1), (2, Backup, Calgary, 1), (3, New benefits, Toronto,2)…}
See Textbook page 92 (Project Table)
Let:
oN be the set of natural numbers
oM be set of names
oL be the set of locations
Then:
PROJECT N x M x L x N
Database Schema (Long answer on conversion of ER to Database Schema on Final)
Defines the columns that you have in your tables
Entities
Entity: an object that exists in the real world
oPhysically: book, car, student
oConceptually: job, route
Entity-Type: a class of entities
oEmployee
oProject
oDepartment
Attributes
Entities have attributes
oProperties that describe entities
An employee can be described by
oSIN
oName (Two people can share the same name, so you can separate it by
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 26 pages and 3 million more documents.

Already have an account? Log in
first name, middle name, last name)
oDOB
oGender
oAddress (You can include the Postal Code, City, Province)
Entity Types in ER Diagrams
See Textbook page 94
We only come up with the fields that are important for that table
Unique Key/Primary Key – every table has one, and it is the one that is underlined
(this is something that distinguishes someone from everyone else) Ex: Student –
ID, Courses – Number
Primary Keys
Primary Key: a collection of attributes the uniquely identify an entity
oOne attribute most of the time
oCan also be a combination of attributes (there can be more than one key)
SIN for employee
Student ID
Underlined in ERD
Clicker Question
Which of the following can be used as a primary key in a CAR entity type:
oThe car make (there are many different honda’s)
oThe car make, model, and year (there are many different cars with the
same made, model and year)
oThe car make, model, year, and owners SIN (the same person can own the
same car, same make, same model, and same year)
oNone of the above **
Cardinality of Relationships (At least 2 questions on the final)
One-to-one
One-to-many
oOne course can belong to one department, and one department can have
many courses
Many-to-one
Many-to-many
oOne student can be in many courses, and one course has many students
Relationship Types
See textbook page 95
Relationship Degrees
The degree of a relationship is the number of entity types it relates
Controls is a binary relationship type
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 26 pages and 3 million more documents.

Already have an account? Log in
Example: PROJECT and DEPARTMENT, and the relationship is CONTROLS
Participation Levels
Entity types participate
oFully (universal participation) represented by a solid line
oPartially (existential participation) represented by a dotted line
oSee Page 97
ERD Examples
See textbook page 97
Online textbook selling
oOne book can be used in many courses, one course can use many books
oYou can have a full or partial participation (a course can not have a book,
but a book must always belong to a course)
Clicker Questions
Which of the following relation is one-to-many
oRegisters in between student and course (one student can be registered in
many courses, and one course can have many students many-to-many)
oLikes between student and student (one student can like many students,
and many students can like one student many-to-many)
oThe biological mother of between student and student (one mother can
have many children who are students, but many students can only have
one biological mother one-to-many) ***
oNone of the above
Mapping ERD’s to Schema
Mapping Algorithm (4.1)
Each entity type is translated to a table; its attributes become columns (Tables are
translated into columns, going from left to right, and underline primary key)
Each many-to-many relationship type becomes a table; the columns are the
primary keys of the participating entity types (Will have the primary key of both
the two tables you are combining)
For each one-to-many relationship type, as the primary keys of the entity type on
the one side as columns in the table corresponding to the entity type on the many
side
See Textbook page 101
Make sure to underline your primary keys
There will be no more tables (For any many-to-many, a new table gets created)
Foreign key (Secondary Key) – something that doesn’t belong to your table, but it
belongs to another table and it is the primary key of that other table
Make sure to highlight the foreign key! **
The mapping algorithm does not include one-to-one relationship types
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 26 pages and 3 million more documents.

Already have an account? Log in

Get access

Grade+
$10 USD/m
Billed $120 USD annually
Homework Help
Class Notes
Textbook Notes
40 Verified Answers
Study Guides
1 Booster Class