Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
many-many, many-one, and one-one erd relationship in sql tables? PLEASE USE SIMPLE CROWS FOOT ERDS!!!

many-many, many-one, and one-one erd relationship in sql tables? PLEASE USE SIMPLE CROWS FOOT ERDS!!!

Database System Concepts
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
icon
Related questions
Question

Does anyone know how to type up a many-many, many-one, and one-one erd relationship in sql tables? PLEASE USE SIMPLE CROWS FOOT ERDS!!!! Also, in the screenshot provided below, my teacher says "FOREIGN KEY (e_ssn) REFERENCES employee (ssn)" at the bottom, what does that mean? If youre having trouble understanding the 1st part of my question, use 2nd shot for reference.

### Weak Entity Primary Key To illustrate handling weak entities in SQL, consider the following example: **SQL Table Creation** We're omitting named constraints due to space limitations. ```sql CREATE TABLE dependent ( d_name VARCHAR(90), e_ssn CHAR(11) NOT NULL, gender CHAR(1), -- I'm changing this birth_date DATE, relationship VARCHAR(20), CHECK(gender IN ('M', 'F', 'N', 'T', 'I', 'P')), PRIMARY KEY(d_name, e_ssn), FOREIGN KEY(e_ssn) REFERENCES employee(ssn) ); ``` **Notes:** - There's no need to verify the `ssn` format since it's a `FOREIGN KEY` and has likely passed the `CHECK` constraint in `employee`. - The `FOREIGN KEY` maintains the relationship necessary for the many-to-one pattern. --- **Diagrams Explanation** In the diagram, there are two tables representing the `Employee` and `Dependent` entities: 1. **Employee Table:** - `SSN`: Format DDD-DD-DDDD - `F_name`: String - `M_init`: Character - `L_Name`: String - `Address`: String - `Salary`: Money - `Sex`: M/F 2. **Dependent Table:** - `Name`: String - `Sex`: M/F - `Birth_date`: Date - `Relationship`: String These tables are linked with a relational arrow indicating `Dependent_of` a specific `Employee`, showing the weak entity relationship based on the primary and foreign key constraints discussed in the SQL code.
Transcribed Image Text:### Weak Entity Primary Key To illustrate handling weak entities in SQL, consider the following example: **SQL Table Creation** We're omitting named constraints due to space limitations. ```sql CREATE TABLE dependent ( d_name VARCHAR(90), e_ssn CHAR(11) NOT NULL, gender CHAR(1), -- I'm changing this birth_date DATE, relationship VARCHAR(20), CHECK(gender IN ('M', 'F', 'N', 'T', 'I', 'P')), PRIMARY KEY(d_name, e_ssn), FOREIGN KEY(e_ssn) REFERENCES employee(ssn) ); ``` **Notes:** - There's no need to verify the `ssn` format since it's a `FOREIGN KEY` and has likely passed the `CHECK` constraint in `employee`. - The `FOREIGN KEY` maintains the relationship necessary for the many-to-one pattern. --- **Diagrams Explanation** In the diagram, there are two tables representing the `Employee` and `Dependent` entities: 1. **Employee Table:** - `SSN`: Format DDD-DD-DDDD - `F_name`: String - `M_init`: Character - `L_Name`: String - `Address`: String - `Salary`: Money - `Sex`: M/F 2. **Dependent Table:** - `Name`: String - `Sex`: M/F - `Birth_date`: Date - `Relationship`: String These tables are linked with a relational arrow indicating `Dependent_of` a specific `Employee`, showing the weak entity relationship based on the primary and foreign key constraints discussed in the SQL code.
### A One-to-One Example In this example, we define the structure of a one-to-one relationship between a "person" table and a "driver_license" table. #### SQL Table Creation The SQL code to create the "person" table is as follows: ```sql CREATE TABLE person ( person_id INT IDENTITY PRIMARY KEY, person_name VARCHAR(100), license INT, CONSTRAINT pr_license_uq UNIQUE(license), CONSTRAINT pr_license_fl FOREIGN KEY REFERENCES driver_license(license_id) ); ``` #### Explanation - `person_id`: This is an integer that serves as the primary key for the "person" table, uniquely identifying each person. - `person_name`: This is a variable character string with a maximum length of 100, used for storing the name of the person. - `license`: An integer that stores the license number associated with a person. - The constraint `pr_license_uq` enforces that each license number is unique within the "person" table. - The constraint `pr_license_fl` sets up a foreign key relationship, connecting the "person" table to the "driver_license" table using `license_id`. #### Diagrams The image displays two tables and their relationships: 1. **Person Table:** - Fields: - `Person_id` (Integer) - `Person_name` (String) 2. **Driver_license Table:** - Fields: - `License_id` (Integer) - `State` (String) - `Expiration_date` (Date) The relationship between the tables is shown through a line labeled "-License_of-" connecting the `license` field in the "person" table to the `license_id` in the "driver_license" table, illustrating the one-to-one relationship between a person and their driver license.
Transcribed Image Text:### A One-to-One Example In this example, we define the structure of a one-to-one relationship between a "person" table and a "driver_license" table. #### SQL Table Creation The SQL code to create the "person" table is as follows: ```sql CREATE TABLE person ( person_id INT IDENTITY PRIMARY KEY, person_name VARCHAR(100), license INT, CONSTRAINT pr_license_uq UNIQUE(license), CONSTRAINT pr_license_fl FOREIGN KEY REFERENCES driver_license(license_id) ); ``` #### Explanation - `person_id`: This is an integer that serves as the primary key for the "person" table, uniquely identifying each person. - `person_name`: This is a variable character string with a maximum length of 100, used for storing the name of the person. - `license`: An integer that stores the license number associated with a person. - The constraint `pr_license_uq` enforces that each license number is unique within the "person" table. - The constraint `pr_license_fl` sets up a foreign key relationship, connecting the "person" table to the "driver_license" table using `license_id`. #### Diagrams The image displays two tables and their relationships: 1. **Person Table:** - Fields: - `Person_id` (Integer) - `Person_name` (String) 2. **Driver_license Table:** - Fields: - `License_id` (Integer) - `State` (String) - `Expiration_date` (Date) The relationship between the tables is shown through a line labeled "-License_of-" connecting the `license` field in the "person" table to the `license_id` in the "driver_license" table, illustrating the one-to-one relationship between a person and their driver license.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 3 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Single Table
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
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
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)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS