1 Database Schema Diagrams - University of Toronto

1 CSCB20 Worksheet Database Schema and relational algebra11 Database Schema DiagramsWe will use the University sample relational Schema defined in lectures. The following schemadiagram illustrates all the relations in the Database . Each relation has primary keys arrows to show allforeign keyconstraints. ID course_id sec_id semester year grad ID name dept_name tot_cred building room_no capacity course_id sec_id semester year building room_no time_slot_id s_id i_id ID course_id sec_id semester year takes section classroom teaches prereq course_id prereq_id course_id title dept_name credits course student dept_name building budget department instructor ID name dept_name salary advisor time_slot time_slot_id day start_time end_time 2 relational AlgebraWe use relational algebra to specifyquerieson a Database .

2 This is the formal mathematical nota-tional. Later we will see how this translates into of relational Algebra1 Diagrams and examples taken from Database System Concepts 6th Operations51 relational ALGEBRAThe relational algebra defines a set of operations on relations, paralleling theusual algebraic operations such as addition,subtractionormultiplication,whi choperate on numbers. Just as algebraic operations on numbers take one or morenumbers as input and return a number as output, the relational algebra op-erations typically take one or two relations as input and return a relation algebra is covered in detail in Chapter 6, but we outline a few ofthe operations (Name)Example of Use!!salary>=85000(instructor)(Selection )Return rows of the input relation that satisfythe predicate.!!ID,salary(instructor)(Projec tion)Output specified attributes from all rows ofthe input relation.

3 Remove duplicate tuplesfrom the output. instructor department(Natural join)Output pairs of rows from the two input rela-tions that have the same value on all attributesthat have the same name. instructor department(Cartesian product)Output all pairs of rows from the two inputrelations (regardless of whether or not theyhave the same values on common attributes) !name(instructor) !name(student)(Union)Output the union of tuples from the two ,wecouldhaveperformedtheoperationsineith erorder, but that is not the case for all situations, as we shall , the result of a query contains duplicate tuples. For example, if weselect thedeptnameattribute from theinstructorrelation, there are several casesof duplication, including Comp. Sci. , languages adhere strictly to the mathematical definition of a set and removeduplicates.

4 Others, in consideration of the relatively large amount of processingrequired to remove duplicates from large result relations, retain duplicates. Inthese latter cases, the relations are not truly relations in the pure mathematicalsense of the course, data in a Database must be changed over time. A relation can beupdated by inserting new tuples, deleting existing tuples, or modifying tuples Lets University Schema and relations are at the end of this What is the result of sid=ID(student advisor)?2. Explain what each of the following expressions do:(a) year 2009(takes)onstudent(b) year 2009(takesonstudent)(c) ID, name, courseid(studentontakes)3. Suppose we have the following relational Database :employee (personname, street, city)works(personname, companyname, salary)company (companyname, city)(a) Find the names of all employees who live in city Miami.

5 Personname( city= Miami (employee))(b) Find the names of all employees whose salary is greater than $100,000. personname( salary>100000(works))2(c) Find the names of all employees who live in Miami and whose salary is greater than$100,000. personname( city= Miami salary>100000(employeeonworks))(d) Find the names of all employees who work for First Bank Corporation . personname( companyname= First Bank Corporation (works))(e) Find the names and cities of residence of all employees who work for First Bank Cor-poration . personname,city( companyname= First Bank Corporation (worksonemployee))(f) Find the names, street address, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000. personname,street,city( companyname= First Bank Corporation salary>10000(employeeonworks))4.

6 Suppose we have the following relational Database :branch(branchname, branchcity, assets)customer(customername, customerstreet, custromercity)loan(loannumber, branchname, amount)borrower(customername, loannumber)account(accountnumber, branchname, balance)depositor(custromername, accountnumber)(a) Underline appropriate primary keys are bold font. Note that some of this is up to interpretation of the Database - feel free to discuss and come to some agreement. A sample solution might be:branch(branchname, branchcity, assets)customer(customername,customerstr eet, customercity) ** customernames maynot be unique**loan(loannumber, branchname, amount) ** assume that loan numbers are across allbranchesborrower(customername,loannum ber)account(accountnumber, branchname, balance)depositor(customername,accountnu mber)(b) Given your choice of primary keys, identify appropriate foreign keys.

7 Draw a relationdiagram to indicate foreign having the students draw a box for each Schema and then draw the relation diagramfor the foreign keys as is done on the diagram on the first (branchname, branchcity, assets)customer(customername,customerstr eet, customercity) ** names may not beunique**loan(loannumber, branchname, amount) ** assume that loan numbers are across allbranchesborrower(customername,loannum ber)account(accountnumber, branchname, balance)depositor(customername,accountnu mber)Foreign Keys:loanandaccounthave foreign foreign foreign keyaccountnumberforaccount.(c) Find the names of all branches located in Chicago . branchname( branchcity= Chicago (branch))(d) Find the names of all borrowers who have a loan in branch Down-town . customername( branchname= Down-town (borroweronloan))(e) Find all loan numbers with a loan value greater than $10, 000.

8 Loannumber( amount>10000(loan))(f) Find the names of all depositors who have an account with a value greater than $6000. customername( balance>6000(depositoronaccount))3 University RelationsRelations and their schemas:48 Chapter 2 Introduction to the relational Modelclassroom(building,roomnumber,capac ity)department(deptname,building,budget) course(courseid,title,deptname,credits)i nstructor(ID,name,deptname,salary)sectio n(courseid,secid,semester,year,building, roomnumber,timeslotid)teaches(ID,coursei d,secid,semester,year)student(ID,name,de ptname,totcred)takes(ID,courseid,secid,s emester,year,grade)advisor(sID,iID)times lot(timeslotid,day,starttime,endtime)pre req(courseid,prereqid)Figure of the University languages used in practice include elements of both the procedural andthe nonprocedural approaches.

9 We study the very widely used query languageSQLin Chapters 3 through are a number of pure query languages: The relational algebra is pro-cedural, whereas the tuple relational calculus and domain relational calculus arenonprocedural. These query languages are terse and formal, lacking the syntacticsugar of commercial languages, but they illustrate the fundamental techniquesfor extracting data from the Database . In Chapter 6, we examine in detail the rela-tional algebra and the two versions of the relational calculus, the tuple relationalcalculus and domain relational calculus. The relational algebra consists of a setof operations that take one or two relations as input and produce a new relationas their result. The relational calculus uses predicate logic to define the resultdesired without giving any specific algebraic procedure for obtaining that OperationsAll procedural relational query languages provide a set of operations that can beapplied to either a single relation or a pair of relations.

10 These operations havethe nice and desired property that their result is always a single relation. Thisproperty allows one to combine several of these operations in a modular , since the result of a relational query is itself a relation, relationaloperations can be applied to the results of queries as well as to the given set specific relational operations are expressed differently depending on thelanguage, but fit the general framework we describe in this section. In Chapter 3,we show the specific way the operations are expressed most frequent operation is the selection of specific tuples from a sin-gle relation (sayinstructor)thatsatisfiessomeparticul arpredicate(saysalary>$85,000). The result is a new relation that is a subset of the original relation (in-444 Chapter 2 Introduction to the relational Modelcourseidsecidsemesteryearbuildingro omnumbertimeslotidBIO-1011 Summer2009 Painter514 BBIO-3011 Summer2010 Painter514 ACS-1011 Fall2009 Packard101 HCS-1011 Spring2010 Packard101 FCS-1901 Spring2009 Taylor3128 ECS-1902 Spring2009 Taylor3128 ACS-3151 Spring2010 Watson120 DCS-3191 Spring2010 Watson100 BCS-3192 Spring2010 Taylor3128 CCS-3471 Fall2009 Taylor3128 AEE-1811 Spring2009 Taylor3128 CFIN-2011 Spring2010 Packard101 BHIS-3511 Spring2010 Painter514 CMU-1991 Spring2010 Packard101 DPHY-1011 Fall2009 Watson100 AFigure shows a sample instance of you can imagine, there are many more relations maintained in a real uni-versity Database .)