2. Part 2 nsider the following database with the below relations: customer-id customer-name 192-83-7465 Johnson 019-28-3746 Smith 677-89-9011 182-73-6091 321-12-3123 Jones 336-66-9999 Lindsay 019-28-3746 Smith Hayes Turner (a) The customer table account-number balance A-101 500 A-215 700 A-102 400 A-305 350 A-201 900 A-217 750 A-222 700 (b) The account table customer-street 12 Alma St. 4 North St. 3 Main St. 123 Putnam Ave. 100 Main St. 175 Park Ave. 72 North St. 34 customer-city Palo Alto Rye Harrison Stamford 336-66-9999 019-28-3746 Harrison Pittsfield Rye customer-id account-number 192-83-7465 A-101 192-83-7465 A-201 019-28-3746 A-215 677-89-9011 A-102 182-73-6091 A-305 321-12-3123 A-217 A-222 A-201 (c) The depositor table What is the difference between SQL and relational algebra? In SQL Environment, what is the difference between DDL and DML Write the Relational algebra expressions (using the relational algebra operators: p, a. ,X) for each of the following queries' a. List the id and name of all customers p. List all information about customers from Harrison C. List the customer_id, account_number and balance for all account.

2. Part 2 nsider the following database with the below relations: customer-id customer-name 192-83-7465 Johnson 019-28-3746 Smith 677-89-9011 182-73-6091 321-12-3123 Jones 336-66-9999 Lindsay 019-28-3746 Smith Hayes Turner (a) The customer table account-number balance A-101 500 A-215 700 A-102 400 A-305 350 A-201 900 A-217 750 A-222 700 (b) The account table customer-street 12 Alma St. 4 North St. 3 Main St. 123 Putnam Ave. 100 Main St. 175 Park Ave. 72 North St. 34 customer-city Palo Alto Rye Harrison Stamford 336-66-9999 019-28-3746 Harrison Pittsfield Rye customer-id account-number 192-83-7465 A-101 192-83-7465 A-201 019-28-3746 A-215 677-89-9011 A-102 182-73-6091 A-305 321-12-3123 A-217 A-222 A-201 (c) The depositor table What is the difference between SQL and relational algebra? In SQL Environment, what is the difference between DDL and DML Write the Relational algebra expressions (using the relational algebra operators: p, a. ,X) for each of the following queries' a. List the id and name of all customers p. List all information about customers from Harrison C. List the customer_id, account_number and balance for all account.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 5TD

See similar textbooks

Similar questions

Based on the following functional dependencies, for the relation SCHEDULE bring it into a set of 3NF relations. Dependencies: Section#, CourseID, Semester ID → RoomNo Section#, CourseID, Semester ID → Inst_ID Section#, CourseID, Semester ID → Week_day Section#, CourseID, Semester ID → Start_time Section#, CourseID, Semester ID → End_time Course_ID → Course Name RoomNo → Occupancy Instr_ID → Instr_name SCHEDULE CourseID, Section#, Semester ID, Course Name ,RoomNo, Occupancy, Inst_ID, Instr_Name, Week_day, Start_time, End_time)
The following is the relation notation of a database table in 1NF ORDER(CustNum, CustFN, CustLN, OrderNum, OrderDate, ProdNum, ProdDesc, Qty, UnitPrice) The functional dependencies are given below: CustNum → CustFN, CustLN OrderNum - OrderDate, CustNum, CustFN, CustLN ProdNum → ProdDesc OrderNum, ProdNum -> UnitPrice, Qty Use the functional dependencies above to answer the following questions. Don't not make additional assumptions and functional dependencies. A. List all partial dependencies in the relation only (if any) using the dependency numbers. Don't write the dependencies in X->Y format, just indicate their numbers (which are listed on the left side, i.e., 1, 2, 3, 4). Indicate only the dependencies that you think are partial. B. Identify and show the 2NF. Show primary keys and foreign keys references. Use relational notation (show references). Write down all relations and their references below. C. List all transitive dependency in your answer to question b (if any)…
Student ID | Student_name | Cource ID | Cource name | Grade 2 Noor 1 HIML B+ 7 C++ A 3 JAVA B 25 Al 2 C+ A 3 JAVA B 27 Sara 1 HIML A 3 JAVA A 28 Abmed 2 C++ B+ 1. What update anomalies may occur in the selation? 2. In which normal form (NF) the above relation? Provide an explanation to support your answer. 3. Decompose the relation so that the resut relations are in the third normal form (3NF).
) Consider the relations r(A, B, D), and s(B, D, F), in which A, B, D and F are their attributes. Thefollowing SQL query based on these two relations has a nested subquery. Provide an equivalent SQL querywhich does not have any nested subquery.select A, Bfrom rwhere r.B < some (select Bfrom swhere s.D= r.D)
STUDENT Student# Std-Name Address 1 Smith Jeddah 2 Bob Buraidah 3 Alice Dammam COURSE Student# Course Code Course-Name 1 IT241 Operating System 2 IT210 Computer Network 2 IT445 Decision Support System Write a query using the Right Outer Join to retrieve the record from the two relations. Also, construct the table displaying the output of your query. Consider the following enterprise scenario and answer the following questions. ABC is a wholesale company that sells electrical equipment and provides a website from which customers can inquiry about products and identify what they want to buy. When costumers order electrical equipment, they place their order on the ABC website. ABC does not own or hold any equipment as inventory. Rather, the ABC orders the equipment from the appropriate supplier and ranges for the equipment to be shipped directly from the supplier to the customers. The…
DATABASE SYSTEMS 11.Which of the following query finds the names of the employees who have taught at least one course? a)SELECT DISTINCT e.ename FROM employee e, course c WHERE e.eid = c.cid; b)SELECT e.ename FROM employee, course WHERE e.eid = c.cid; c)None of these d)SELECT e.ename FROM employee e, course c WHERE e.eid = c.cid; 12.If A-> B, C,D,E and D -> E are functional dependencies of the relation R(A,B,C,D,E) Then identify the best normal form of the given relation R. a)2NF b)BCNF c)3NF d)1NF 13.The attributes of relationship completes in employee completes course should be a)empid, cno, cname, grade b)empid, cno, grade c)empid, ename, dept, phone no d)cno, cname, grade 14.What is the meaning of LIKE 'a%a%' a)Feature ends with two a's b)Feature begins with two a's c)Feature has two a's in it, at any position d)Feature begins with a and has at least one a's in it at any position 15.If in JOIN operation, conditions of JOIN operation are not satisfied then results of…
Consider the following two relations for Millennium College:STUDENT(StudentiD StudentName,CampusAddress, GPA)REGISTRATION(Student!D CourseiD Grade)Following is a typical query against these relations:SELECT Student_T.StudentiD, StudentName,CourseiD, GradeFROM Student_T, Registration_TWHERE Student_T.StudentiD =Registration_ T.StudentiDANDGPA>3.0ORDER BY Student Name;a. On what attributes should indexes be defined to speedup this query? Give the reasons for each attributeselected.b. Write SQL commands to create indexes for each attribute you identified in part a.
Which of the following functional dependencies are satisfied (and not satisfied) by this relation and Why? (a) A → B Why? (b) A → CD Why? (c) AB → CD Why?
Consider a database that consists of the following relations.SUPpLIER(Sno. Sname)PART(Pno, Pname)PROJECT(Jno, Jname)SUPPLY(Sno, Pno, Jno)The database records information about suppliers, parts, and projectsandincludes a ternary relationship between suppliers, parts, and projects.Thisrelationship is a many-many-many relationships. Specify and execute thefollowingqueries using the relational algebraRetrieve the part numbers that are supplied to exactly two projects.b. Retrieve the names of suppliers who supply more than two parts toproject J11c. Retrieve the part numbers that are supplied by every supplier.d. Retrieve the project names that are supplied by supplier S1' only.e. Retrieve the names of suppliers who supply at least two different partseach to at least two different projects.
You are given a database containing Customer, Service, Call and Rates tables to provide some results for MMC. (That is, your CC will provide you with the database). You should: 1. Rename the database as MGMT2006_G# , where G# is your group number. 2. Create relationships between pairs of tables and enforce referential integrity. 3. Add TWO new Customers and associate them with a Service. 4. Also add TEN new Calls along with their information for any ten customers. Be sure to include calls for your two additional customers. 5. Create the following queries: a. List the names of customers whose status is disconnected. Name this query Q1 – NOTCONNECTED. b. What is the total duration of calls, pre-paid and post-paid for customers with data-plans and for customers without data-plans? Round the duration to a fixed number with no decimal places. Sort in descending order by duration. Name this query Q2 – CallDuration. c. For each customer who has a post-paid service, show how many of these…
Consider the follow relations’ schemas to answer the following questions: Student (sid, name, level, address) Book(bid, title, author) BorrowedBook(sid, bid, date) 1) Assume that, there are 10,000 Student records stored on 1,000 pages/blocks, 50,000 Book records stored on 5,000 pages, 300,000 BorrowedBook records stored on 15,000 pages, and there are 500 different authors. Find out what is the record size for each of the previous relation?, and what is the blocking factor for each relation?
Closely examine the following tables and use it to answer the questions that follow:departmentstudent_type student_categorycourseStudentregistration registration_typeSuppose that the following are true:- all id column are primary keys in whichever table they are- the dept_id in course table is foreign key that is referencing the id column in the department table- the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columnsin the student_type and student_category tables respectively.- the coursecode and stud_id columns in the registration table are foreign keys in the courseand student tables respectively.1. Design an Entity Relationship Diagram for the system above.2. Write query that would return the names and contacts of all Local students.3. Write a query that would return names, contact and locations of all departments that has alocation text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”]4. What would be the result returned…

SEE MORE QUESTIONS

Recommended textbooks for you

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS