Students(stullD, lastName, firstName, major, credits) • Faculty(faciD, name, deptName, rank) . Classes(classNumber, facID, schedule, room) . Enrolls(stulD, classNumber, grade) The primary keys are underlined. The referential integrity constraints are as follows: the column facID of relation Classes that references table Faculty, the column stulD of relation Enrolls that references table Students, and the column classNumber of relation Enrolls that references table Classes. The following query is intended to retrieve all courses in which fewer than three students are enrolled. However, this query does not work properly. Briefly explain why: _ SELECT classNumber FROM Enrolls WHERE COUNT(*)>3 GROUP BY classNumber; and show the corrected SQL query

Students(stullD, lastName, firstName, major, credits) • Faculty(faciD, name, deptName, rank) . Classes(classNumber, facID, schedule, room) . Enrolls(stulD, classNumber, grade) The primary keys are underlined. The referential integrity constraints are as follows: the column facID of relation Classes that references table Faculty, the column stulD of relation Enrolls that references table Students, and the column classNumber of relation Enrolls that references table Classes. The following query is intended to retrieve all courses in which fewer than three students are enrolled. However, this query does not work properly. Briefly explain why: _ SELECT classNumber FROM Enrolls WHERE COUNT(*)>3 GROUP BY classNumber; and show the corrected SQL query

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

Inventory Database: Customers(custid, custName, address, phone, creditLimit) Orders(orderNo, orderDate, totalAmount, custID). products(prodNo, prodName, unitPrice) Includes(ordan No, prodNo, ordered Qantity). The primary keys are underlined. The referential integrity constraints are as follows: • the column custID of relation Orders that references table Customers, • the columns orderNo of relation Includes that references table orders, and • the column prodNo of relation Includes that references table Products. For the Inventory Database in the reference, write the SQL command to display the ID, name, and address of the customer who placed the order number A03012022. For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph T "S Arial 10pt 一田田日用因 AV > तं (0) I % 0 QE Q >
Inventory Database: Customers(custld, custName, address, phone, creditLimit) Orders (orderNo, orderDate, totalAmount, custID) products (prodNo, prodName, unitPrice) Includes(orderNo, prodNo, orderedQantity). The primary keys are underlined. The referential integrity constraints are as follows: • the column custID of relation Orders that references table Customers, • the columns orderNo of relation Includes that references table orders, and the column prodNo of relation Includes that references table Products. For the Inventory Database in the reference, write the SQL command to display the customer ID and the number of orders for each customer. For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac). BIUS Paragraph ¶"Q 1. 10pt 30 = 2 CHAG OCZ « † ( Ⓒ IXO Q Ô + бе
A) The following relations kind a part of a on-line database (the relations area unit self- explanatory): CUSTOMER/CLIENT/SHOPPERS/BUYERS(Cust#, CAddress, Ctown, Balance, Cname) ORDER(Order.# Date, Cust#.) ORDERLINE(Order#, Part#, Amount-Ordered) PART(Part#., Desc, Stock, Item-Class, Unit -Price) a. Design a physical ER for the above scenario. b. With respect to this relative schema, write SQL statements for the subsequent queries: 1. List the name of the client United Nations agency has placed Order 123 (i.e. Order: = 123). 2. List the names of shoppers United Nations agency in Cardiff and have ordered half 456 (i.e. Part. 456). 3. List the order numbers (Order) for orders that contain a minimum of three elements and also the quantity ordered are minimum of ten. 4. List the outline (Desc), of these elements that the typical quantity ordered is bigger than the number ordered for half .6 (Part. = 456) so as 123.(Order..123).
Identify the normal form it is in. Please make sure to provide explanations to support your answer. Convert it to BCNF. 1.GRADES (S#, C#, sem, year, F#, FName, Sname, Cname, grade, crHrs) a) Write down the following functional dependencies: S# --> C# --> F# --> b) Which normal form is the above relation in? c) Convert it to BCNF: 2.TEACHES (C#, sem, year, F#, Fname, Foffice) a) Write down the functional dependency for F#: F# --> b) Which normal form is the above relation in? c) Convert it to BCNF 3. R (a,b,c,d,e,f,g) Given that: d --> a e --> f,g a) Which normal form is the above relation in? b) Convert it to BCNF:
SpecificationFunctional DependencyConsider the relational schemas given below and the respective sets of functional dependencies valid in the schemas. For each one of the relational schemas identify its highest normal form. Remember that the identification of a normal form requires analysis of the valid functional dependencies and the minimal keys. FLIGHT(passenger, phone#, flight#, seat#, departure-place,departure-time)The values of attributes of a relational table FLIGHT satisfy the following properties:- a passenger has one phone number,- a passenger books seats on many different flights,- the attributes flight#, seat#, and departure place uniquely identify each passenger,- only one flight takes of from a given departure place at a given departure time.
Primary keys and candidate keys may both be assigned to the same attribute.
# Question 1#Using the World database, in the city table, insert a new city for the Tasmania district. The city name is ‘Devonport’, #country code is ‘AUS’, district is ‘Tasmania’ and population is 25,387. Remember, the ID is set to auto-increment #and you will not assign a value. You will insert 2 snips for this question: #a. Insert a snip of the construct used. This MUST be a snip of the code used to insert this city into the city table. #b. Run a query to display the entire new row of Devonport inserted in the City table. Insert your snip here:
Second normal form (2NF) states which of the following? All fields must contain single values only. Part of the primary key may not determine a non-key field. A non-key field may not determine a non-key field. In an all-key table, part of the key can determine multiple values of, at most, one other field.
STORY BOOK # Id * Name * Published date o Publisher * Type Find the error in the given ERD written by write O g. Novel name is multivalued attribute Oh. Type attribute must be in AUTHOR side O i. Cardinalities of relationships are not correct AUTHOR # Id * Name * Surname o Age O a. There is a volatile attribute and it must be converted to nonvolatile attribute O b. "Published date" and "Publisher" attributes are violating 3NF O c. Name attribute cannot be used in both entities since it is not allowed to have same name O d. Name of the "STORY BOOK" must be written as "STORY_BOOK" O e. Optionality of relationship in AUTHOR side cannot be mandatory O f. "Surname" attribute can be optional
branch(branchhame, branch+citysassets} customer (HD, Customer name, customer street, customer city) loan loan number branch_name, amounty borrower(ID; loan_number account (account_number, branch name, balance)e depositor (HDs account number)- Figure le Consider the bank database in Figure 1, where the primary keys are underlined. Each branch might have many loans or accounts, associated with borrowers or depositors, respectively. Construct the following SQL queries for this relational database. 1. Find the name of each branch that has at least one customer who has an account in the bank and who lives in the "Harrison" city. Make sure each branch name only appears once. 2. Find the ID of each customer who lives on the same street and in the same city as customer "12345". (1) Please use "tuple variables". e (2) Please use "derived relations" or “with". 3. Find the ID of each customer of the bank who has an account but not a loan. 4. Find the total sum of all loan amounts for each branch…
113. The template conclusion is a set of tuples that must be included in the relations in the template dependency called a. constraint generating templates b. tuple generating templates c. schema generating templates d. relation filtering template
Q1. explain the relation between them VERYBRIEFLY: => [span and live time], [scope and principle of proximity]