Consider the following relations for a database that keeps track of orders: orderLines(lineNumber, productID,orderNumber,quantity) products (productID,productName,price,categoryID) orders(OrderNumber,customerID,order Date) categories(categoryID,categoryDescription) List the foreign keys that are in these relations (The FKs are not currently identified any attributes are foreign keys to relations that are not listed above. There are three relationships in the above four relations. One of the relationships is orderLines and products, the second is between orderLines and orders, and the third products and categories. Specify the attributes that would be foreign keys in these relations.

Consider the following relations for a database that keeps track of orders: orderLines(lineNumber, productID,orderNumber,quantity) products (productID,productName,price,categoryID) orders(OrderNumber,customerID,order Date) categories(categoryID,categoryDescription) List the foreign keys that are in these relations (The FKs are not currently identified any attributes are foreign keys to relations that are not listed above. There are three relationships in the above four relations. One of the relationships is orderLines and products, the second is between orderLines and orders, and the third products and categories. Specify the attributes that would be foreign keys in these relations.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 3TD

See similar textbooks

Similar questions

What is a relation?
//Last three parts not answered before You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project. The schema for the database used in this question is as follows: ( primary keys are shown underlined, foreign keys in bold). SUPPLIER (SNo, SupplierName, City) PART (PNo, PartName, Weight) PROJECT (JobNo, JobName, StartYear, Country) SUPPLY (SNo, PNo, JobNo, Quantity) 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 . do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the quantity of parts supplied on JobNo 4745. List the weight of the Part named “Left-handed screwdriver”. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a particular…
ER Mapping : Convert the ER diagram into a relational database schema. For each relation, specify the primary keys and the foreign key(s) if any (50 pts) Upload you Relational Diagram here BANK BRANCHES BANK-BRANCH Code Name Addr Addr BranchNo АССTS LOANS N AcctNo Balance LoanNo Amount ACCOUNT Туре LOAN Type M M A-C L-C N Name N. SSN Phone CUSTOMER Addr
Assignment Brief: Some of the most difficult decisions that you face as a database developer are what tables to create and what columns to place in each table, as well as how to relate the tables that you create. Normalization is the process of applying a series of rules to ensure that your database achieves optimal structure. Normal forms are a progression of these rules. Each successive normal form achieves a better database design than the previous form did. Based on your understanding for the various normal form respond to the following problems and provide the optimal answer/solution. Problem#1: 1st Normal Form (1NF) Consider the Students table, with the primary key underlined, and the following data: Students: Alpha 100111 Email doe@usna.edu Name Courses GradePoints NN204, SI204, IT221 SM223, EE301 SI204 John Doe 2,3,3 Matt Smith 092244 113221 smith@usna.edu black@usna.edu 4,4 Melinda 3 Black NN204, SI204, IT221 090112 Tom Johnson Johnson@usna.ed 4,2,3 u a) Is the Students table…
For developing our queries, we will be using the following relational schema: Student(SID, name, username, major) Course(cname, description, department, weight) Offering(OID, cname, year, term, duration) Enrolled(SID, OID, grade) A course relation is the generic description of a course which is offered by a department. The offering relation is the specific instance of a course being taught in a given term, and is the entity which will be associated with the student enrollment. Students are enrolled in these offerings and achieve a mark. Express each of following queries in Relational Algebra: | Write a relational algebra query which will list the cname and description of all the courses which are being offering in the Fall 2018 term. 2 Write a relational algebra query which will list the name of all the students who have earned 60% in both 'COSC2P12' and 'COSC2P13'.
Consider the following relations database schema. The primary key is underlined, and all attributes are of type string if not indicated otherwise. branch(branch_name, branch_city) customer(customer_id, customer_name,customer_street,customer_city) account(account_number, branch_name, balance: integer) depositor(customer_id, account_number) Write SQL queries to: Find all the branch names with customers who have accounts of balance less than $2000 and the branch is located in LA. Delete all customers who do not have an account. (Assume the bank keeps the customer information even if the account is closed.
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. A) List the first and last names of customers who have bought “Back Scratcher” and “Hair Remover” B) List the first and last names of any customers who have bought…
QUESTION 5 Consider the following relations in a University database: Student (SID, SName, Age, GPA) Course (CID, CName, Lecturer) Enrollment (SID, CID, Grade) SID is the primary key of Student, and CID is the primary key of Course (SID, CID) is the primary key of Enrollment, where SID is the foreign key referencing Student and CID is the foreign key referencing Course. CName is a unique field in the Course relation. Given the following SQL query: SELECT S.SName, E. Grade FROM Student S, Course C, Enrollment E WHERE S.SID = E.SID AND C.CID = E.CID AND C.CName = 'Database'; Show the initial query tree based on the conceptual evaluation strategy. Show the most efficient query tree after applying all the five steps of rule-based query tree optimization. (1) (2) Break up conjunctive selection into a cascade of selection operators; • Push down selection operators; . Convert cross-products into joins; Rearrange leaf nodes to execute the most restrictive selection operators first, Push down…
A composite primary key's prospective key is an attribute?
Consider the following relations for a simple airport databas Hangars (hangNo, capacity, location, status) AirplaneTypes (Model, capacity, weight) Airplanes (regNo, model, hangNO) Owners (owner, name, address, phone, ssn) Owns (regNo, ownID, startedDate) There are four foreign keys for this database: 1. The attribute hangNo of the relation Airplanes that references the relation Hangars, 2. The attribute model of the relation Airplanes that references the relation AirplaneTypes, 3. The attribute regNo of the relation Owns that references the relation Airplanes, and 4. The attribute ownID of the relation Owns that references the relation Owner. Q1: Write SQL statements to define the above relation Hangars. You should choose a suitable data type and/or daya size for each column and be sure to declare appropriate integrity constraint(s). Q2. Which of the following SQL statements is used to define the above relation Airplanes? Ensure the data type and size for each attribute are…
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. Each question is worth 2 marks. NOTE: You can use the symbolss, 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 first and last names of Customers who come from the City named Perth. List the first and last names of customers who had…
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…