Study Guides (238,085)
Canada (114,909)
CPSC 203 (1)
All (1)

CPSC Final Notes.doc

26 Pages
Unlock Document

University of Calgary
Computer Science
CPSC 203

Database & Data Modeling Databases • Organized collection of data • Minimizes redundancy o Wastes 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: o N be the set of natural numbers o M be set of names o L 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 o Physically: book, car, student o Conceptually: job, route • Entity-Type: a class of entities o Employee o Project o Department Attributes • Entities have attributes o Properties that describe entities • An employee can be described by o SIN o Name (Two people can share the same name, so you can separate it by first name, middle name, last name) o DOB o Gender o Address (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 o One attribute most of the time o Can 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: o The car make (there are many different honda’s) o The car make, model, and year (there are many different cars with the same made, model and year) o The car make, model, year, and owners SIN (the same person can own the same car, same make, same model, and same year) o None of the above ** Cardinality of Relationships (At least 2 questions on the final) • One-to-one • One-to-many o One course can belong to one department, and one department can have many courses • Many-to-one • Many-to-many o One 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 • Example: PROJECT and DEPARTMENT, and the relationship is CONTROLS Participation Levels • Entity types participate o Fully (universal participation) represented by a solid line o Partially (existential participation) represented by a dotted line o See Page 97 ERD Examples • See textbook page 97 • Online textbook selling o One book can be used in many courses, one course can use many books o You 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 o Registers in between student and course (one student can be registered in many courses, and one course can have many students many-to-many) o Likes between student and student (one student can like many students, and many students can like one student many-to-many) o The 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) *** o None 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 o We need to include these • Sometimes, relationship types may need to have • Add the start date to “manages”, and hours to “works on”, these are both attributes and aren’t part of an actual table, just the relationship between) • See Textbook page 104 (One-to-one relationship: one department can only have one manager, and one manager can only have one department) Not every employee is a manager, which makes it a partial participation, but every department has a manager, which makes it a full participation. Design Principles Basic Design Principle Page 105 • Meaning of a Schema should be easily explained • Reduce Redundancy • Reduce NULL values Design Principle (1) • Design a schema so that its meaning is easily explained • Do not combine attributed from different entity-types into a single table Design Principle (2) • Design a schema so that Redundancy is reduced • Unnecessary Redundancy can lead to modification anomalies Unnecessary Redundancy • Assume the schema definition for Project and Department o Migrating Pnumber Design Principle (3) • Design a schema so that NIULL values are minimized as much as possible • Waste space • Result in confusion: o A NULL value could mean:  Does not apply  Unknown  To be recorded • ** Make sure there is no NULL values in your Assignment 2 NULL Values Confusion • Textbook page 107 Database Queries Structured Query Language • SQL • Programming language, specialized for databases • Data Definition Language (DDL) • Defining the structure of the DB • Data Manipulation Language (DML) • Manipulating the contents of the DB DDL • Ex: Textbook Page 135 • CREATE TABLE EMPLOYEE • SIN CHAR(9) • Fname CHAR (15) • Lname CHAR (15) • DOB Date • GenderCHAR (6) • Salary Number • Street CHAR • City CHAR • PCode CHAR • DNumber Number DML – Insertion • Example • INTERT INTO EMPLOYEE VALUES • (171717171, ‘Debra’, ‘Beacon’, ’15-Aug-1961’, ‘Female’, 70000, 15, ‘Baron Hill’, ‘Calgary’, ‘T2X Y0Y, 1) • Order is important • Make sure to have quotations around texts, but not numbers • 123 type number • ‘name’ type character DML – Delete • DELETE FROM EMPLOYEEWHERE Gender = ‘Male’ SQL Queries (At least two long answers and a couple multiple choice on final) Queries • Questions submitted to the DB • Ex: What is my mark on this particular quiz • Query By Example (QBE) • Using SQL QBE – Projection • SELECT Department.Dnumber, Department.Dname • FROM Department SQL – Projection • SELECT Dnumber, Dname • FROM DEPARTMENT QBE – Selection • Example: Textbook Page 111 • SELECT Pnumber, Pname, Location, Dnumber • FROM PROJECT • WHERE Location = ‘Calgary’ • (Everything gets deleted except where it is Calgary) Wild Cards • SELECT * the * means everything • FROM PROJECT • WHERE Location = ‘Calgary’ • The output will give you all four different columns and it will view the entire data where the location is equal to Calgary Set Operations • Textbook Page 115 • SELECT * • FROM PROJECT • WHERE Location = ‘Calgary’ • SELECT * • FROM PROJECT • WHERE Location = ‘Toronto’ Union in Access • Cannot be done in design vie, only in SQL view SQL – Set Operations • SELECT SIN • FROM EMPLOYEE • WHERE Gender = ‘Female’ • INTERSECT • SELECT MGR_SIN • FROM DEPARTMENT Clicker Question • Which English sentence best describes the following query: o SELECT SIN o FROM EMPLYEE WHERE GENDER = “Female” o INTERSECT o SELECT SIN FROM PROJ_EMP WHERE Hours >10 o SIN of all female employees who work on a project less than 20 hours • Which English sentence best describes the flowing query o SELECT SIN FROM EMPLOYEEWHERE SALARY < 50000 o MUUS o SELECT SINFROM PROJ_EMP WHERE HOURS > 20 o SIN of all employees who earn less than 50K and do not work on a project more than 20 hours INTERSECT and MINUS in Access • No direct support • Can use IN for intersect • Can use NOT IN for minus INTERSECT in Access • SELECT SIN FROM employee WHERE Gender = “Female” • AND SIN IN • (SELECT MAG_SIN FROM DEPARTMENT); How IN Works • It first does the inner query that you have (the part in the brackets), once the first query is done, then you do the outer query and perform the intersection on the two of them MINUS in Access • SELECT EMPLYEE.Sin FROM EMPLOYEE WHERE (((EMPLOYEE.Sin) Not In • (SELECT DEPARTMENT Comparison Operators • Equals = • Not Equal != • Greater > • Greater or Equal >= • Less < • Less or Equal <= Boolean Operators • AND (true when both A=true and B=true) • OR (false when A=false or B=false) • NOT (true is A=false) SQL – Example Query • Textbook page 121 • SELECT SIN, Lname, Fname FROM EMPLYEE WHERE Salary >= 30000 • AND Salary <= 50000 Clicker Question • Which query retrieves all employees who do not work for Department 1 and live in Calgary, and those who work for Department 1 but live outside Calgary? o SELECT * FROM EMPLYEE WHERE (DNUMBER != 1 AND City = “CALGARY”) OR (DNUMBER = 1 AND City != “CALGARY”) You have two conditions and if only one is correct then the statement is correct (the or means one or the other has to be true Multi-Table Queries (Final Exam Question) • PROJECT x DEPARTMENT • SELECT * • FROM Project, Department; • You have Project 1 and you cross it with Department 1, 2, 3, and 4; You have project 2 and you cross it with Department 1, 2, 3, and 4; and so on … You get 12 results Natural Join Condition • Textbook Page 123 • Ex: SELECT * FROM PROJECT, DEPARTMENT WHERE PROJECT.Dnumber = DEPARTMENT.Dnumber Join Example • Textbook Page 123 (Example 4.8) • Retrieve the address of each employee f the IT department SELECT Number, Street, City, Pcode FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.Dnumber = DEPARTMENT.Dnumber AND Dname = “IT” Determining the result of a join query • Textbook page 125 • It will take your first condition, Dname = Marketing, and then it will satisfy the condition of Salary > 70000 • You should be able to come up with a query like the one in this example on your exam Clicker Question • If DEPARTMENT has 6 rows and EMPLYEE has 100 rows, how many data rows does the following query return? SELECT * FROM DEPARTMENT, EMPLOYEE o 600 it will return each and every element that there is (it will take every department and match it up with a employee) o 6 x 100 = 600 Aggregate Functions and Grouping (question on the final) Order By Clause • Orders the result of a query SELECT * FROM EMPLYEE WHERE City != “Calgary” ORDER BY Lname Descending Order • Textbook Page 126 Example 4.9 SELECT * FROM EMPLYEE WHERE City != “Calgary” != means does not equal ORDER BY Lname DESC Otherwise, if you don’t put DESC, it will automatically do it ascending Aggregate Functions • Most of the aggregate functions only return one row of data • Textbook Page 127 • Example 4.10 • Sum, Avg, Count, Min, Max o You use 3 tables, because the PROJ_EMP is the only was the EMPLOYEE and the PROJECT relate (the order doesn’t matter how you write it down) o **Expect a question using 3 tables on your exam (like the max example) • You need to figure out how the data relates. Clicker Questions • How many rows does the following query return? o SELECT AVG(Price) FROM BOOK o It will always return one line for you, no matter how many rows you have o It will take the average price of the books, even if there are 100 books, you will still only get one row of results Grouping Calculations • How to find the sum or salary per department • SQL has the GROUP BY clause • Group calculations of an aggregate function GROUP BY • Textbook Page 128 Example 4.11 • If you GROUP BY Dnumber, also try and SELECT Dnumber HAVING Clause (Expect a question like this on your final exam***) • To show only some of the groups • WHERE filters tuples • HAVING filters groups • Textbook Page 129 Example 4.12 o The HAVING Clause, returns the results that it counts all the employees who work on more than one project Clicker Question • Which statement best describes the following query? SELECT Location COUNT (Pnumber) FROM PROJECT GROUP BY Location Having COUNT (Pnumber) > 1 • Find all cities that have more than one project Programming Algorithms • Algorithms can be specified in different ways • Here we focus on specifying algorithms using a programming language Programming Levels (A few multiple choice on the final exam) • Computers understand 0s and 1s o Machine Language o Low Level (LL) language • Programmers use High Level (HL) languages o Easier to understand and work with o What you as a programmer can understand Translation • Translator: a program that translates HL to LL code • Two types: Compilers and Interpreters • Compilers: translate the whole HL program to LL program • Interpreters: translate the HL program to LL program, one instruction at a time (will do one section at a time) Alice • Object oriented Language • An Alice program is called a world • Everything in the scene is an object o Cow, T-Rex, mill, grass • An object is similar to an entity in Databases o It has properties (attributes) – color, height o In addition, it has behavior – stay, turn around • Cow objects have at least the properties: o Size and color • Different objects may have different values for properties but the properties themselves will be the same o One brown cow, one white cow • Objects have behavior • A cow can talk, walk, and eat • A method is a small program • The behavior is specified as a collection of methods. • A method is a small program o All the objects to do something specific • A class is similar to an entity type in a database o It is the blueprint • Objects are call instances of a class • Creating on object instantiates the class • The cow has 2 components o Neck and Body o Further composed of other objects • The Objects exhitii a certain behaviour through executing their methods o Calling their method o Cow (Object).Turn (subobject), trex.turn, trex.walk, windmill(object).blades(object).roll(method) Clicker Question • Given that FIDO and Milo are both my neighbors dogs. Which of the following sentences is true? o F&M are instances of class Dog CORRECT o Didn’t share properties o F&M are examples of class o F&M are objects in natural discourage • Must have a storyline before you start programming o 1) in the openfield with a windmill in the background o 2) a trex approaches from behind o ETC • Whenever you run your world for Alice o Main program – where Alice starts o World.myfirstmethod *main program  This method is executed when Alice is run • A variable is a named memory location to hold a value • Most common data types of variable in Alice o Number: age [123] o Boolean: true or false [T/F] o Object: cow, trex [OBJ] o String: name [ABC] • Name, Value, Type, String are 4 types of values Exam Question • Test= 10 • X = 1 • Print x • Print Text • Increment text by 1 • Print Test • Memory to Solve • 2 variable – 10 & 1 • Memory • Test = 10 • X = X = 10 • What is the output of above? o *looking for print statement* • Print x = 10 • Print text = 10 • Increment means add 1 = 11 Property Variable • Properties Local Variables • Local to methods • Do not exist outside the methods in which they are contained. Assignment Statement & User Input • Resize: created a variable • Assignment Systems are assigned variables Methods • A method is defined inside an object o Resize, turn, say • It has a name o Cow, penguin • And zero or more parameters o Left, right • A method is called within an object: Parameters • Parameters of a method are like local variables in the method • The difference is that a value must be assigned to parameters when a method is called o (resize) 4; 0.5 revolutions; Trex, I will show you; durations = 2 seconds Functions • Functions are methods that return values • Mathematically, f(n) = 2 • F(5) returns 10 • A method doesn’t return back a value, where as a function will return back a value Sequential Executions • The program we have created is sequential • Straight-line code • Finish a method before starting the next Parallel Execution • Two methods executed at the same time o In parallel, or concurrently o The windmill blades roll concurrently with the rest of the movie actions o Cow turns her head while Trex turns away • You can have one or more actions that happen at the same time Do In Order • Default mode of execution • Can group statements in a Do In Order Statement Do Together • Can group statements in a Do together statement to run concurrently Intertwining in order and together (review for Quiz 3) • Textbook page 158 If-Else Statements Conditional Execution • Programs need to make decisions o Ex: Whether your username and password match • An ATM program must verify your password to know how to proceed • This is called conditional Execution • The execution of a group of statements is conditional on a Boolean condition o Must be true or false • If the password is correct then go to the next step • Else give an error message If-Else Flowchart • Textbook page 159 If-Else Statements in Alice • Textbook page 161 Loops Loop Statement (Keep in mind for quiz and final) • A loop statement allows a group of statements to be repeated for a number of times • Textbook page 161 • Ex: loop for 5 times Monkey.mokneyJump Monkey.move(up, 1 meter) Monkey.move(down, 1 met
More Less

Related notes for CPSC 203

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.