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.37PAE
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)
Because of referential integrity, before any can be entered into the Section table, the course ID to be entered must already exist in the COURSE table. Write an SQL assertion that will enforce this constraint.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Consider each of the takes and teaches relations as a fact table; they do not have an explicit measure attribute, but assume each table has a measure attribute reg_count whose value is always 1. What would the dimension attributes and dimension tables be in each case. Would the resultant schemas be star schemas or snowflake schemas?
Transform the given ER model into a set of 3NF relations. Use the format shown below for relations.
FACULTY (FacID, LastName, FirstName, Rank, DeptID)
FK DeptID à DEPT
DEPT (DeptID, DeptName, DeptPhone)
You must underline all components of each primary key. Denote each foreign key directly beneath the relation that contains the foreign key, as shown above. Do not use the graphical notation from the textbook. The notation shown here is the notation you will be required to use on exams. Be sure that all relations are in 3NF.
******HOW DO WE DRAW THE MULTI VALUED ATTRIBUTE
Provide instances of relations (tables) that may have many candidate keys.
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
- 2. a. What characteristics do tables that conform to the concept of well-informed relations have? b. Describe a dependency diagram and explain its purpose c. Normalize the relation given below Order Orde PartN | Part Unit PartQ | Supplie Suppli Suplli Suppl Suppli Supplier rDat umbe Nam Pric uantit rNumb erNam erStre Num ierCit erSateZip e e e er etarrow_forwarda. What characteristics do tables that conform to the concept of well-informed relations have? b. Describe a dependency diagram and explain its purpose c. Normalize the relation given below OrderNum OrderDate PartNumber PartName UnitPrice PartQuantity SupplierNumber SupplierName SupllierStreet SupplierCitr SupplierSate SupplierZiparrow_forwardPart 2: Relational data model. Take a subset of the ideas from the conceptual model you constructed in Part 1 and design a simple relationship model similar to the ones we discussed in Module 2, Video 4 Your model should have at least 5 tables You should include at least 20 attributes, or fields, in your model (20 total across all tables, not per table) Your model should be normalized Identify the primary key in each table, and state whether it is a natural or surrogate key For each relationship between tables, identify any foreign keys needed to define the relationship For each table, identify what type of system or systems you think the data might come from, like those we discussed in Module 1, Video 6.arrow_forward
- The many-to-many relationship between entities is not supported by which data model? Please explain the model's advantages and disadvantages.arrow_forwardUse MS Visio to create relational data schemas and data dependency diagrams for all entities (tables). Start out with listing all data attributes in 1NF, then label partial dependencies and potential transitive dependencies. For this Data. A database for payment and bookings for a Virginia Beach hotel. A guest can check into a hotel. A hotel can have many guests. A hotel can keep a record of a guest’s activities. A record can contain many notes. Possible tables might include: Guests, Notes, Room Type, Charges. Possible Primary keys might include: GuestID, NoteID, RoomID, ChargeCode. Possible attributes might include: CustName, Room#, ChargeType.arrow_forwardA.What characteristics do tables that conform to the concept of well-informed relations have? B Describe a dependency diagram and explain its purpose C. Normalize the relation given below OrderNum OrderDate PartNumber PartName UnitPrice PartQuantity SupplierNumber SupplierName SupllierStreet SupplierCity SupplierSate SupplierZiparrow_forward
- Combine the views of the student enrolment in the Enrollment Record and Assessment Forms and represent the unnormalized integrated enrolment record (together with the assessment) using relational notation. Perform normalization to convert the enrolment record to 3NF, showing all intermediate stages (i.e. 1NF and 2NF). Represent the normalized relations using relational notation. Document all assumptions that you make about the data model.arrow_forwardUse the database shown in Figure P3.10 to work Problems 10-16. Note that the database is composed of four tables that reflect these relationships: An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEES. An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many EMPLOYEES. Note also that the M: N relationship has been broken down into two 1: M relationships for which the BENEFIT table serves as the composite or bridge entity. For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None. Create the ERD to show the relationship between EMPLOYEE and JOBarrow_forwardYou are required to: Provide a normalized relational logical schema for the same database (NB It is not mandatory to report the restructured ER diagram) Define referential integrity constraints for 2 relationships of your choice among those defined in the conceptual schema solution: AUTHOR( author_id , name, surname) BOOK ( ISBN , title , num_page s , price ) CUSTOMER( customer_id , phone_number*, type, name*, surname*, vat_number*) CLERK( clerk_id , name) BUY( customer_id, clerk_id, ISBN ) WRITTEN_BY( author_id, ISBN ) 1) WRITTEN_BY (author_id) REFERENCES AUTHOR(author_id) WRITTEN_BY (ISBN) REFERENCES BOOK(ISBN) 2) BUY(customer_id) REFERENCES CUSTOMER(customer_id) BUY (clerk_id) REFERENCES CLERK(clerk_id) BUY (ISBN) REFERENCES BOOK (ISBN) i gave the solution. but i do not have any idea where it came from and how im supposed to formulate a similar solution so please help me understand how was this was reached and how i should solve these type of questions in…arrow_forward
- With suitable example, explain what is Data Redundancy. Consider the relation Treatment with the schema Treatment (doctorID, doctorName, patientID, diagnosis) and functional dependencies; doctorID doctorName patientID diagnosis D001 Mohan PAT123 Fever D002 Vijay PAT110 Allergy D003 Jenifer PAT112 Fever D002 Vijay PAT121 Cold Describe the three (3) types of anomaly that can arise for this table with example records.arrow_forwardHelp normalize this into 4NF and make a diagram please? Or just explain the relations I should do. Here is the guide to do normalization * Each relation (table) represents a single subject. For example, a COURSE table will contain only data that directly pertain to courses. * Each row/column intersection contains only one value and not a group of values. * No data item will be unnecessarily stored in more than one table (tables have minimum controlled redundancy). * All nonprime attributes in a relation (table) are dependent on the primary key - the entire primary key and nothing but the primary key. * Each relation (table) has no insertion, update, or deletion anomalies, which ensures the integrity and consistency of the data.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_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Fundamentals of Information SystemsComputer ScienceISBN:9781337097536Author:Ralph Stair, George ReynoldsPublisher:Cengage Learning
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Enhanced Entity Relationship Model; Author: Data Science Center;https://www.youtube.com/watch?v=ocQUtXPumdQ;License: Standard YouTube License, CC-BY