1. Given the relation as the following figure: (5 pts.) ASSIGN_NUM EMP_NUM PROJ_NUM 1001 1002 1003 1004 1005 1006 1007 1

[answerhappygod]

1 Given The Relation As The Following Figure 5 Pts Assign Num Emp Num Proj Num 1001 1002 1003 1004 1005 1006 1007 1 1 (99.46 KiB) Viewed 40 times

1. Given the relation as the following figure: (5 pts.) ASSIGN_NUM EMP_NUM PROJ_NUM 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 103 117 117 103 108 104 113 103 115 117 105 108 115 104 103 18 22 18 18 25 22 25 18 15 15 25 18 25 22 15 1.1 What is a primary key? (1 pts.) PROJ_NAME Amber Wave Rolling Tide Amber Wave Amber Wave Starflight Rolling Tide Starflight Amber Wave Evergreen Evergreen Starflight Amber Wave Starflight Rolling Tide Evergreen PROJ_VALUE 3500500.0 805000.0 3500500.0 3500500.0 2650500.0 805000.0 2650500.0 3500500.0 1453500.0 1453500.0 2650500.0 3500500.0 2650500.0 805000.0 1453500.0 1.2 Find the two samples of functional dependencies (FDs)? (2 pts.) 1.3 There is a data redundancy occurring in this relation or not and why? (2 pts.)
2. Given the relation R(A, B, C, D, E) and FD: { AB-->D, B-->C, DC-->E} (5 pts.) Find the following closure of attribute: 2.1_{A}+ (1 pts.) 2.2 (BC)+ 2.3_{AD}+ 2.4 What is a candidate key? (1 pts.) (1 pts.) (2 pts.)
3. Consider the following relational database: (5 pts.) branch(branch name, branch city, assets) customer (customer name, customer street, customer city) loan (loan number, branch name, amount) borrower (customer name, loan number) account (account number, branch name, balance) depositor (customer name, account number) Find an expression in the relational algebra of the following queries: 3.1 Find the name of customers who have amount of loan less than 100,000. 3.2 Find the account numbers that have a zero balance. 3.3 Find the name of customers who deposits the account in branch name "KC". 3.4 Find the name of customers who deposit in account number = 100100. 3.5 Find the name of branches where the customer names "John Smith" borrows the money?
4. According to the database description: The movies are rented out in stores and there are several stores. Each store has a unique distributor that supplies the store with disks. A distributor may supply more than one store. Each distributor has a name, an address, and a phone number. Each store has a name, an address, and a phone number. For each employee we must keep the following information: working store, a name, a supervisor, an address, a phone number, SSN (social security number). For each customer we keep the following information: a name, an address, and a phone number. For each rental, we must keep track of which employee served the customer, which movie and which copy (i.e. disk) the customer rented, information about payment, the date and time of the rental, the status (rented, returned in time, returned late), the rate (i.e. the price), and if applicable, due date and overdue charges. About the payment we keep which of the employees accepted the payment (does not have to be the same employee who rented the disk), the type of payment (i.e. cash, check, credit card, direct debit), the amount of the payment, date and time of the payment, payment status. About each disk, we keep information in what condition the disk is and what movie is on the disk. About each movie we keep its title, director's name, simple description, the name of a (single) major star, the movie's rating. Use the Movie Rental Description to answer the following questions. 4.1 Design the relational data model using Chen's Model (5 pts.) 4.2 Design the relational data model using Crow's Foot Model_(5 pts.)