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.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

The database used for this question is a very simple one with the following schema: (Primary keys are bold, foreign keys are underlined) CUSTOMER (CustID, FirstName, LastName, City, Phone, Email) INVOICE (InvoiceNumber, CustID, Date) INVOICE_ITEM(InvoiceNumber, ItemNumber, Quantity) ITEM (ItemNumber, ItemName, UnitPrice) When a customer makes a purchase, an invoice is created. The invoice may be for many items. For example, in a single purchase, a customer might buy 10 Back Scratchers, 4 Hair Removers and a Dog Lead. Provide relational algebra (NOT SQL) queries to find the following information. NOTE: You can use the symbols s, P, etc., or the words’ PROJECT’, ‘RESTRICT’ etc. as you prefer. You do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the dates on which Homer Griffin made purchases. List the first and last names of customers who have bought “Back Scratcher” or “Hair Remover” List the first…
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)…
Consider the following Books database which is used to keep track of books and their readers. Assume that the database is composed of the following three relations where the key attributes are underlined. BOOK Title PERSON READS PID 111 111 222 333 444 BID PID 111 Gender BookID StartDate 2/4/2015 PYear Price Name 2 Internet programming Database System Programmin g using C++ 1 2016 50 Sami M 3 2/6/2015 8/4/2017 Khawla Rawan Zainab Khamis 222 2 2013 45 4 333 444 2 2/6/2015 3 2011 25 555 M 2 2/4/1995 444 1 2/8/1995 Operating Systems Oracle DBMS 4 2009 35 5 2017 50 • Write a SQL query to display the information including title, name (person name), and PID of all the books which have been read (StartDate) in the year 2015, and the price of these books is less than 50. Convert the generated SQL query into a Relational algebra query. Then, draw the optimized query tree (fifth step of query optimization). Note: You can draw a tree on a paper, scan, and upload in the Moodle OR you can describe…
The following three tables make up a simple reservation system for a small campground. The database should allow for a camper to make multiple reservations for future dates and for a camping spot to have several reservations for upcoming visits. Table: Camper CAMPER_NUMBER 1000 1001 1002 1003 CAMPER_LAST_NAME Jones Schmidt Williams Cooper CAMPER_FIRST_NAME Jamie Pat Clifford Amanda CAMPER_ADDRESS 1278 Essex Pl 4954 Spangled Way 956 Segull Lane P. O. Box 998877 CAMPER_CITY Birmingham El Paso Portland Portsmouth CAMPER_STATE AL TX ME OH CAMPER_ZIP_CODE 45251 79919 4108 45662 CAMPER_DRIVERS_LICENSE JJ998743-98 87632434 WIL885123 765A876B897 CAMPER_EMAIL jjones@somewhere.com patwonderfu34l@nowhere.net williams98342@foomail.com coopera@nowhere.net Table: Spot SPOT_NUMBER 101 102 103 104 SPOT_NAME The Pines The Glade Teardrop Spot Tent In Trees…
The following relations are part of a general store database: customer(custnum, custname, city, creditlimit) orders(ordernum, orderdate, filled, custnum) orderline(ordernum, productnum, quantity, salesprice) product(productnum, descr, producttype, msrp, onhand) Here is a sample data (assume there are more records)Using PROJECT, SELECT and JOIN, write the sequence of operations to answer each of the following questions: 1a. What are the names and customer numbers of customers who are from Mississauga? 1b.What are the names of products (descry) and product types of products purchased by customers from brapton?
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?
DATABSE
7. A “natural join" in a relational database takes two tables of information and combines entries from the tables that share a particular field. You can read more about this at Wikipedia. Interpret the tables below as sets of sets? For the Course Enrollment Table define A = {{A38182818, Spring2021, Math230, 01}, ..., {A56363631, Spring2021, Math454, 01}}, and for the Student Table define B = {{Al, A81723653}, {Jo, A38182818}, {Su, A56363631}}. Course Enrollment Table Student Table A38182818 | Spring2021 | Math230 | 01 A81723653 | Spring2021 | Math350 01 Al A81723653 A81723653 Fall2020 Math152 04 Jo A56363631 A38182818 Fall2020 Math332 02 Su A38182818 A56363631 | Spring2021 Math454 | 01 (a) Construct the set C = {xUy : x € A, y E B, x N y # 0}. (b) Represent the resulting set C from part (a) as a table, similar to what you see above. Make sure to write down the table for C in a reasonably sorted order. (c) Why would you want to query the database for the set {x € C : Al e x}? Be…
Question A city college would like to maintain their academic information in a MySql database system. The following are the interested information: Courses: Catalog # (M170 for instance) Credit Hours; Name of the Course (Database Design Fundamentals for instance) Course Descriptions Prerequisite Components (Lecture, or Laboratory for instance) Classes: Class # Class Name (usually the course name, M 170 for instance) Start Date End Date Meeting time (9:30 am ~ 12:15 pm, for instance) Days in a week (Monday) Term (Spring 2018, for instance) Location (TIE Building for instance) Room (UB 301, for instance) Instructor Online status (online, or in-person) Faculty: Employee Id (001880301, for instance) First Name Last Name Title (professor, for instance) Email Phone Office Location (TIE building, for instance) Room (UB303, for instance) Student: Student ID First Name Last Name Plan Sub Plan Advisor Current GPA Grade of each course The Term you got the grade for a course. The class…
Don't copy again and again that is wrong. Please help me with this question.
Normalize the following given data to 1/2/3 NF. State the relations schema after everynormalization.