Modern Database Management (12th Edition)
12th Edition
ISBN: 9780133544619
Author: Jeffrey A. Hoffer, Ramesh Venkataraman, Heikki Topi
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 6, Problem 6.38PAE
Are based on the class scheduling 3NF relations along with some sample data shown in Figure 6-11. Not shown in this figure are data for an ASSIGNMENT relation, which represents a many-to-many relationship between faculty and sections.
Note that values of the SectionNo column do not repeat across semesters.
Figure 6-11 Class scheduling relations (missing ASSIGNMENT)
Write SQL data definition commands for each of the following queries:
a. How would you add an attribute, Class, to the Student table?
b. How would you remove the Registration table?
c. How would you change the FacuttyName field from 25 characters to 40 characters?
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
The attribute on which a relation is partitioned can have a significant impacton the cost of a query.a. Given a workload of SQL queries on a single relation, what attributeswould be candidates for partitioning? b. How would you choose between the alternative partitioning techniques, based on the workload? c. Is it possible to partition a relation on more than one attribute? Explain your answer
Using SQL data definition language, write the SQL statements to change each of the following relations according to the suggestions given below.i. customer (customer id, customer name, street, city)Suggestion: Remove customer name and add lname, fname and initial.ii. account (account number, customer id, branch id, balance)Suggestion: Add a foreign key constraints for customer id and branch id, referencing customer and branch tables.iii. branch(branch id, branch name, branch city, assets)Suggestion: Add the not null constraint to branch name and branch city.
(( I Need Assignment 2 and 3 only ))
Customer Order
Assignment #1
Create tables based on the below given relational model.
Define your own data type to the attributes and apply your own constraints to the attributes
Identify the primary, foreign keys apply it to the attributes.
Populate the data in all the tables using SQL commands/queries.
Customer Order tables:
Customer (customerNO, customerName, address, city, category, custBalance)
Product (productNO, productTitle, price, QStock )
customerOrder ( orderNO, orderDate, customerNO)
orderedProduct (orderNO, productNO, orderQuantity)
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
Assignment #2
Function:
Create a function which accepts customer no and display the total number of orders placed.
Sample output is:
The Total customer orders are 4
Procedure
Write a Procedure that accepts…
Chapter 6 Solutions
Modern Database Management (12th Edition)
Ch. 6 - Prob. 6.1RQCh. 6 - Prob. 6.2RQCh. 6 - Contrast the following terms: a. base table; view...Ch. 6 - Prob. 6.4RQCh. 6 - Prob. 6.5RQCh. 6 - Prob. 6.6RQCh. 6 - List six potential benefits of achieving an SQL...Ch. 6 - Prob. 6.8RQCh. 6 - Distinguish among data definition commands, data...Ch. 6 - Prob. 6.10RQ
Ch. 6 - Prob. 6.11RQCh. 6 - Prob. 6.12RQCh. 6 - Prob. 6.13RQCh. 6 - Drawing on material covered in prior chapters,...Ch. 6 - Explain and provide at least one example Of how to...Ch. 6 - Prob. 6.16RQCh. 6 - What is the difference between COUNT, COUNT...Ch. 6 - What is the evaluation order for the Boolean...Ch. 6 - If an SQL statement includes a GROUP BY clause,...Ch. 6 - Prob. 6.20RQCh. 6 - Prob. 6.21RQCh. 6 - Prob. 6.22RQCh. 6 - Explain why SQL is called a set-oriented language.Ch. 6 - When would the use of the LIKE key word with the...Ch. 6 - Prob. 6.25RQCh. 6 - Prob. 6.26RQCh. 6 - In what order are the clauses of an SQL statement...Ch. 6 - Within which clauses of an SQL statement can a...Ch. 6 - Prob. 6.29RQCh. 6 - Prob. 6.30RQCh. 6 - Prob. 6.31RQCh. 6 - Explain the difference between the WHERE and...Ch. 6 - Prob. 6.33RQCh. 6 - Prob. 6.34PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.36PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.40PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.42PAECh. 6 - Prob. 6.43PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.45PAECh. 6 - Prob. 6.46PAECh. 6 - Prob. 6.47PAECh. 6 - Prob. 6.48PAECh. 6 - Prob. 6.49PAECh. 6 - Prob. 6.50PAECh. 6 - Prob. 6.51PAECh. 6 - Prob. 6.52PAECh. 6 - Prob. 6.53PAECh. 6 - Modify the Product_T table by adding an attribute...Ch. 6 - Prob. 6.55PAECh. 6 - Add an order to the Order_T table and include a...Ch. 6 - Use the Pine Valley database to answer the...Ch. 6 - Prob. 6.58PAECh. 6 - Prob. 6.59PAECh. 6 - Prob. 6.60PAECh. 6 - Prob. 6.61PAECh. 6 - Prob. 6.62PAECh. 6 - Prob. 6.63PAECh. 6 - Prob. 6.64PAECh. 6 - Prob. 6.65PAECh. 6 - Prob. 6.66PAECh. 6 - Prob. 6.67PAECh. 6 - Prob. 6.68PAECh. 6 - Prob. 6.69PAECh. 6 - Prob. 6.70PAECh. 6 - Prob. 6.71PAECh. 6 - Prob. 6.72PAECh. 6 - Prob. 6.73PAECh. 6 - Prob. 6.74PAECh. 6 - Prob. 6.75PAECh. 6 - Prob. 6.76PAECh. 6 - Prob. 6.77PAECh. 6 - Prob. 6.78PAECh. 6 - Prob. 6.79PAECh. 6 - Prob. 6.80PAECh. 6 - Prob. 6.81PAECh. 6 - Prob. 6.82PAECh. 6 - Prob. 6.83PAECh. 6 - Prob. 6.84PAECh. 6 - Prob. 6.85PAE
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- In SQL, foreign key constraints can reference only the primary key attributes ofthe referenced relation or other attributes declared to be a superkey using theunique constraint. As a result, total participation constraints on a many-to-manyrelationship set (or on the “one” side of a one-to-many relationship set) cannotbe enforced on the relations created from the relationship set, using primarykey, foreign key, and not null constraints on the relations. Explain whyarrow_forwardproduce a list of relations indicating the primary key, columns and foreign keys. Consider the following attributes for tables in a relational model designed to keep track of information for a moving company that moves residential customers, usually from one home or apartment to another:customerID, customerName, customerCurrentAddress, customerCurrentPhone, customerNewAddress, customerNewPhone, pickupLocation, dropOffLocation, dateOfMove, startingTime, estimatedWeight, estimatedCost, truck#Assigned, driverName, driverLicNo, actualCost, amountOfDamages, truckCapacity, costOfTolls, tax, finalAmount, invoiceNumber, amountPaid, datePaid, checkNumber, amountDueAssume:- Although in most cases the pickupLocation is the customer’s old address and the dropOffLocation is the new address, there are exceptions, such as when furniture is moved to or from storage.-An estimate is provided before the move using a pre-printed invoice containing a unique invoice number. The actual cost is recorded on…arrow_forwardConsider the following relational data model. Employee (empid, ename, address, title, headid) Works (Empid,Deptid,joindate) Department (Deptid, dname, block) Payment (title, salary) A) Write relational algebra for Display the employee name and department name of each employee. Display the employee who works as senior lecturer and belongs to BEI or BCT department. Display the employee name with corresponding department head. B) Write sql query for Creating the above relations; including appropriate versions of all primary and foreign key constraints Find the employee name and address whose address name ends with substring ‘pur’. Update the salary of senior lecturer by 25%.(Solve A - 1,2,3 only)arrow_forward
- Create a relational schema model and relations in SQL with all required constraints. Also attach screenshot of each query executed in Oracle (add your roll no. with each table name to avoid plagiarism).arrow_forward((( I Need Assignment 2 And 3 Only ))) Car Insurance Assignment #1 · Create tables based on the below given relational model. · Define your own data type to the attributes and apply your own constraints to the attributes · Identify the primary, foreign keys apply it to the attributes. · Populate the data in all the tables using SQL commands/queries. Car insurance tables: person (driver-id, name, address) car (license, year, model) accident (report-number, date, location) participated(driver-id, license, report-number, damage-amount) ------------------------------------------------- ------------------------------------------------- Assignment #2 Function: · Create a function which accepts driver id and display the total number of accidents. Sample output is: The total number of accidents are: 20 Procedure Write a Procedure that accepts driver id and list the damaged amount, date and location. Package · Create a package which contains…arrow_forwardThe task is to write SQL statements to define tables for the corresponding relations. Make sure that the data and size for each attribute is appropriate. An example of this is how the data type DATE is a better choice than a string data type for the attribute dayOfGame. Additionally, SQL statements to load data into each table based on the given records must be written down. Goals: Write SQL statements to remove tables from the database. Due to foreign key constraints, the sequence of removing tables should be in the reverse order of creating tables. Write SQL statements to define the three tables in the database showcased in the image. Each table should exactly have one PRIMARY KEY. The PRIMARY KEY may have more than one column. Besides the PRIMARY KEY, the table GameStats has two FOREIGN KEYs. Write SQL statements to display the structure of the newly built tables. Write SQL statements to load records into the tables. Add one more record for each table.arrow_forward
- PLZ help with the following: Translate your ER diagram into a relational schema in the form of SQL DDLs. Choose appropriate data types for each attribute and include primary key and foreign key constraints, Check and Not Null constraints. Normalize the relations to 3NF.arrow_forwardIn SQL, foreign key constraints can reference only the primary key attributes ofthe referenced relation or other attributes declared to be a superkey using theunique constraint. As a result, total participation constraints on a many-to-manyrelationship set (or on the “one” side of a one-to-many relationship set) cannotbe enforced on the relations created from the relationship set, using primarykey, foreign key, and not null constraints on the relations. Explain how to enforce total participation constraints using complexcheck constraints or assertions (see Section 4.4.8). (Unfortunately, thesefeatures are not supported on any widely used database currently.)arrow_forwardWrite a relational mode for each of the case below. You can create our own attributes and provide common attributes to determine the link between tables. Draw the Crow’s Foot solution of the business rules given below: A driver may receive many (driving) assignments. Each (driving) assignment is made for a single driver. A truck may be driven in many (driving) assignments. Each (driving) assignment is made for a single truckarrow_forward
- Hospital tables Assignment #1 Create tables based on the below given relational model. Define your own data type to the attributes and apply your own constraints to the attributes Identify the primary, foreign keys apply it to the attributes. Populate the data in all the tables using SQL commands/queries. Hospital tables: patients (patient-id, name, insurance, date-admitted, date-checked-out) doctors (doctor-id, name, specialization) test (testid, testname, date, time, result) doctor-patient (patient-id, doctor-id) test-log (testid, patient-id) performed-by (testid, doctor-id) Assignment #2 Function: Create a function which accepts patient id and display the patients name, date admitted. Sample output is: The patient name is Maryam, she got admitted on 12-Feb-2020 Procedure Write a Procedure that accepts patient id and display the patient id, name, test name and result. Package Create a package which contains the above created procedure in the specification. Create package…arrow_forwardConvert the diagram into a set of relational tables. The tables must be in 3NF. All referential integrity and functional dependencies must be indicated accurately in EITHER of the following 2 ways: Draw lines for referential integrity and for functional dependencies as belows. 1. Show referential integrity as follows: Supplier(S#, Sname, City, Status); Part(P#, Pname, Color, Weight, City); SP(S#, P#, QTY) Foreign key (S#) References Supplier, Foreign key (P#) References Part; 2. Show functional dependencies as follows: B → C CD → Earrow_forwardConsider the following relational schema:Course (cid, cname, dept, credit)Student (sid, sname, dept, gpa)Enroll (cid, sid, grade, semester) The meaning of these relations is straightforward and the keys are underlined. Write thefollowing queries in SQL. d) Find the name s of students who took courses offered by CS department and noother department. e) Find the names of all classes that have 10 or more students enrolled.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education
dml in sql with examples; Author: Education 4u;https://www.youtube.com/watch?v=WvOseanUdk4;License: Standard YouTube License, CC-BY