Today’s Class

1. Database Systems and Applications Today’s Class Relational Model SQL

2. Banking Example branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

3. Examples Reserves Sailors Boats

4. View Definition • A relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called a view. • A view is defined using the create view statement which has the form create view v as < query expression > where <query expression> is any legal SQL expression. The view name is represented by v. • Once a view is defined, the view name can be used to refer to the virtual relation that the view generates.

5. Example Queries create view all_customer as(select branch_name, customer_namefrom depositor, accountwhere depositor.account_number = account.account_number ) union(select branch_name, customer_namefrom borrower, loanwhere borrower.loan_number = loan.loan_number ) • A view consisting of branches and their customers • Find all customers of the Perryridge branch select customer_namefrom all_customerwhere branch_name = 'Perryridge'

6. Uses of Views • Hiding some information from some users • Consider a user who needs to know a customer’s name, loan number and branch name, but has no need to see the loan amount. • Define a view (create view cust_loan_dataasselect customer_name,borrower.loan_number, branch_namefrom borrower, loanwhere borrower.loan_number = loan.loan_number) • Grant the user permission to read cust_loan_data, but not borrower or loan • Predefined queries to make writing of other queries easier • Common example: Aggregate queries used for statistical analysis of data

7. Processing of Views • When a view is created • the query expression is stored in the database along with the view name • the expression is substituted into any query using the view • Views definitions containing views • One view may be used in the expression defining another view • A view relation v1 is said to depend directlyon a view relation v2 if v2 is used in the expression defining v1 • A view relation v1 is said to depend on view relation v2if either v1 depends directly to v2 or there is a path of dependencies from v1 to v2 • A view relation v is said to be recursive if it depends on itself.

8. View Expansion • A way to define the meaning of views defined in terms of other views. • Let view v1 be defined by an expression e1 that may itself contain uses of view relations. • View expansion of an expression repeats the following replacement step: repeatFind any view relation vi in e1 Replace the view relation vi by the expression defining viuntil no more view relations are present in e1 • As long as the view definitions are not recursive, this loop will terminate

9. With Clause • The with clause provides a way of defining a temporary view whose definition is available only to the query in which the withclause occurs. • Find all accounts with the maximum balance withmax_balance (value) asselectmax (balance)fromaccountselectaccount_numberfromaccount, max_balancewhereaccount.balance = max_balance.value

10. Complex Queries using With Clause withbranch_total (branch_name, value) asselectbranch_name, sum (balance)fromaccountgroupbybranch_namewithbranch_total_avg(value) asselectavg(value)frombranch_totalselect branch_namefrombranch_total, branch_total_avgwherebranch_total.value >= branch_total_avg.value • Find all branches where the total account deposit is greater than the average of the total account deposits at all branches. • Note: the exact syntax supported by your database may vary slightly. • E.g. Oracle syntax is of the formwithbranch_totalas ( select .. ),branch_total_avgas ( select .. )select …

11. E –R Model

12. E-R Diagrams • Rectangles represent entity sets. • Diamonds represent relationship sets. • Attributes listed inside entity rectangle • Underline indicates primary key attributes

13. Entity With Composite, Multivalued, and Derived Attributes

14. Relationship Sets with Attributes

15. Roles • Entity sets of a relationship need not be distinct • Each occurrence of an entity set plays a “role” in the relationship • The labels “course_id” and “prereq_id” are called roles.

16. Cardinality Constraints • We express cardinality constraints by drawing either a directed line (), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set. • One-to-one relationship: • A student is associated with at most one instructor via the relationship advisor • A student is associated with at most one department via stud_dept

17. One-to-One Relationship • one-to-one relationship between an instructor and a student • an instructor is associated with at most one student via advisor • and a student is associated with at most one instructor via advisor

18. One-to-Many Relationship • one-to-many relationship between an instructor and a student • an instructor is associated with several (including 0) students via advisor • a student is associated with at most one instructor via advisor,

19. Many-to-One Relationships • In a many-to-one relationship between an instructor and a student, • an instructor is associated with at most one student via advisor, • and a student is associated with several (including 0) instructors via advisor

20. Many-to-Many Relationship • An instructor is associated with several (possibly 0) students via advisor • A student is associated with several (possibly 0) instructors via advisor

21. Participation of an Entity Set in a Relationship Set • Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set • E.g., participation of section in sec_course is total • every section must have an associated course • Partial participation: some entities may not participate in any relationship in the relationship set • Example: participation of instructor in advisor is partial

22. Alternative Notation for Cardinality Limits • Cardinality limits can also express participation constraints

23. E-R Diagram with a Ternary Relationship

24. Exercise • What are the mapping cardinalities of the following 4 relationships? B C D A

25. A University Database • Design an entity-relationship diagram that describes the following objects in a university application: students, professors, and courses. Students take a course in a particular semester and receive a grade for their performance. Sometimes students take the same course again in different semester. There are no limits on how many courses a student can take, and on how many students completed a particular course. Each student has exactly one advisor, who must be a professor, whereas each professor allowed being the advisor of at most 20 students. Courses have a unique course number and a course title. Students and professors have a name and a unique SSN. Students additionally have a GPA and a single or multiple major.

26. A movie studio might have several film crews. The crews might be designated by a given studio as crew 1, crew 2, and so on. However, other studios might use the same designations for crews, so the attribute number is not a key for crews. Rather, to name a crew uniquely, we need to give both the name of the studio to which it belongs and the number of crew. The key for the weak entity set Crews is its own number attribute and the name attribute of the unique studio to which the crew is related by the many-one Unit-of relationship.

27. Exercise 2.4 A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dnameand budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company. Draw an ER diagram that captures this information.