Database Systems: Design, Implementation, & Management
12th Edition
ISBN: 9781305627482
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Question
Chapter 3, Problem 6P
Program Plan Intro
Primary Key:
A Primary Key in a
Example:
Students in Universities are assigned a unique registration number.
Therefore, in a STUDENT database table, the attribute “reg_no” acts as primary key.
Foreign Key:
Foreign Key is a column in a relational database table which provides a relation between two tables. It provides a cross reference between tables by pointing to primary key of another table.
Example:
In STUDENT database table, the attribute “reg_no” acts as primary key and in COURSE database table in which the student selects his or her course, the same “reg_no” acts as foreign key for the STUDENT table.
Many to one relationship:
When one record in a database table is associated with one or more records in another table, the relationship between the two tables is referred as many to one relationship. It is also represented as M:1 relationship.
RELATIONAL DIAGRAM:
Relational Diagram is also known as Entity Relational Diagram. It is used to define the conceptual view of the database as viewed by the end user. It is used to depict the database’s main components: entities, relationships and attributes. It describes how data is related to each other.
Given database tables:
Table Name: STORE
| STORE_CODE | STORE_NAME | STORE_YTD_SALES | REGION_CODE | EMP_CODE |
| 1 | Access Junction | 1003455.76 | 2 | 8 |
| 2 | Database Corner | 1421987.39 | 2 | 12 |
| 3 | Tuple Charge | 986783.22 | 1 | 7 |
| 4 | Attribute Alley | 944568.56 | 2 | 3 |
| 5 | Primary Key Point | 2930098.45 | 1 | 15 |
Table Name: REGION
| REGION_CODE | REGION_DESCRIPT |
| 1 | EAST |
| 2 | WEST |
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
For table EP, the attribute is a foreign key referencing target table For table EP, the attribute is a foreign key referencing target table For table ER the attribute is a foreign key referencing target table For table ER the attribute is a foreign key referencing target table
Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the diagram below
The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names:
All lower case
Underscore separator between root and suffix
Foreign keys have the same name as referenced primary key
Write CREATE TABLE and ALTER TABLE statements that:
Implement the entity as a new phone table.
Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables.
Remove the existing phone column from the Sakila address table.
Step 2 requires adding a foreign key constraint to an existing table. Ex:
ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;
Specify data types as follows:
phone_id, phone_number, and country_code have data type INT.
phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'.
Apply these…
Please may you answer the following questions.
Create a Venn diagram with any topics of your choice and answer the following
Q1: SQL Set Operation UNION: Choose any two or more of the data sets in your Venn diagram. Which elements would be included in the union of those data sets?
Q2: SQL Set Operation INTERSECT: How would you describe one segment of your Venn diagram that represents the intersection of at least three of your data sets? Q3: SQL Set Operation EXCEPT: Choose any two or more of the data sets in your Venn diagram. Find an outlier element. Describe what excluding this outlier element using except logic means for your data sets.
Q4: SQL Join Operation - INNER: Using all of the data sets in your Venn diagram, which segment represents the results of an inner join? If this does not apply to your specific Venn diagram, explain why not. Q5: SQL Join Operation - LEFT: Using any two of the data sets in your Venn diagram, how would you describe the results of a left join? Q6:…
Chapter 3 Solutions
Database Systems: Design, Implementation, & Management
Ch. 3 - What is the difference between a database and a...Ch. 3 - Prob. 2RQCh. 3 - Prob. 3RQCh. 3 - What are the requirements that two relations must...Ch. 3 - Which relational algebra operators can be applied...Ch. 3 - Explain why the data dictionary is sometimes...Ch. 3 - A database user manually notes that The file...Ch. 3 - Prob. 8RQCh. 3 - Prob. 9RQCh. 3 - Prob. 10RQ
Ch. 3 - Prob. 11RQCh. 3 - Prob. 12RQCh. 3 - Use Figure Q3.13 to answer Questions 1317. FIGURE...Ch. 3 - Create the table that results from applying a...Ch. 3 - Write the relational algebra formula to apply an...Ch. 3 - Create the table that results from applying an...Ch. 3 - Using the tables in Figure Q3.13, create the table...Ch. 3 - Prob. 18RQCh. 3 - Prob. 19RQCh. 3 - Prob. 20RQCh. 3 - Identify and describe the components of the table...Ch. 3 - Identify the primary keys. FIGURE Q3.22 THE...Ch. 3 - Identify the foreign keys. FIGURE Q3.22 THE...Ch. 3 - Create the ERM. FIGURE Q3.22 THE CH03_THEATER...Ch. 3 - Create the relational diagram to show the...Ch. 3 - Prob. 26RQCh. 3 - What would be the conceptual view of the INDEX...Ch. 3 - Prob. 1PCh. 3 - Prob. 2PCh. 3 - Do the tables exhibit referential integrity?...Ch. 3 - Describe the type(s) of relationship(s) between...Ch. 3 - Prob. 5PCh. 3 - Prob. 6PCh. 3 - Prob. 7PCh. 3 - Prob. 8PCh. 3 - Create the relational diagram to show the...Ch. 3 - Prob. 10PCh. 3 - Prob. 11PCh. 3 - Create the relational diagram to show the...Ch. 3 - Prob. 13PCh. 3 - Do the tables exhibit referential integrity?...Ch. 3 - Prob. 15PCh. 3 - Prob. 16PCh. 3 - For each table, identify the primary key and the...Ch. 3 - Prob. 18PCh. 3 - Do the tables exhibit referential integrity?...Ch. 3 - Identify the TRUCK tables candidate key(s). FIGURE...Ch. 3 - For each table, identify a superkey and a...Ch. 3 - Prob. 22PCh. 3 - Prob. 23PCh. 3 - Prob. 24PCh. 3 - Create the ERD. (Hint: Look at the table contents....Ch. 3 - Prob. 26PCh. 3 - Prob. 27PCh. 3 - Prob. 28PCh. 3 - Create the table that would result from applying...Ch. 3 - Create the table that would result from applying...Ch. 3 - Create the table that would result from applying a...
Knowledge Booster
Similar questions
- Using tables named T1 and T2, write a query example for each of the three join types you described in Question 2. Assume that T1 and T2 share a common column named Cl.arrow_forwardProblems 27 and 28 are based on the following query: SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE P_QOH P_MIN AND P_MIN = P_REORDER AND P_REORDER = 50 ORDER BY P_QOH; What indexes would you recommend? Write the commands to create those indexes.arrow_forwardWhat is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?arrow_forward
- What three join types are included in the outer join classification?arrow_forward1) List all the functional dependencies of the above table. (Note, this requires you to state all the functional dependencies, not just count the number). 2) What normal form(s) is the table currently in? Justify your answer.arrow_forwardCreate a diagram for a database with at least 5 tables. This should show your table names and column names. Show the relationships between the tables. Write a statement explaining your database design (why did you use the tables that you did). Create a database in Access based on your diagram. Include all tables in your diagram. Tables should each have a minimum of 5 rows. Create 5 SQL statements using your database. At least 2 of these should be manipulation statements (insert, update, etc). The others should be select statements. Explain what your sql statements do and how they would be useful to your database.arrow_forward
- Create a new table named PRODUCT. The product table will have two attributes; product ID as integer, not null, and a primary key constraint set to unique. product description as varchar(30), not null specification. I am not sure how to do a primary key contraint set to unique. I believe I did the first two correct.arrow_forwardThree tables are given Student: studentNO, firstName, lastName, studentProgram Professor: professorID, professorProgram, professorName Student_professor: studentNO, professorID, mentor Complete the following requirements: Using the UNION operator, do a full outer join to display the student last name student program, professor name and professor program. Referring to the model created in step 2, determine the PK/FK relationships to build the SQL joining the three Create a query/subquery to display student first name, last name and program. The student program must be same as that in the professor table and the professor program name starts with a ‘C’. This query will use a subquery and not a join.arrow_forwardImplement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply…arrow_forward
- Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply…arrow_forwardImplement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply…arrow_forwardWhat data types would you use for the attributes in this entity? How would you code a CREATE TABLE statement to implement this entity as a table in the database?arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
COMPREHENSIVE MICROSOFT OFFICE 365 EXCEComputer ScienceISBN:9780357392676Author:FREUND, StevenPublisher:CENGAGE L
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning