Concept explainers
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.
One-to-Many Relationship:
When one record in a database table is associated with more than one record in another table, the relationship between the two tables is referred as one-to-many relationship. It is also represented as1: M relationship. This is the opposite of many to one relationship.
One-to-One relationship:
When one record in a database table is associated with one and only one record in another table, the relationship between the two tables is referred as one-to-one relationship. It is also represented as1: 1 relationship.
ENTITY- RELATIONSHIP DIAGRAM (ERD):
Entity- Relationship Diagram is also known as ER 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.
CROW FOOT ERD:
The Crow Foot ERD is an Entity Relationship Diagram which is used to represent the cardinalities present in the basic ER diagram. It is used to represent the relationships present between two tuples or tables present in the database.
Given database tables:
Table Name: EMPLOYEE
EMP_CODE | EMP_TITLE | EMP_LNAME | EMP_FNAME | EMP_INITIAL | EMP_DOB | STORE_CODE |
1 | Mr. | Williamson | John | W | 21-May-84 | 3 |
2 | Ms. | Ratula | Nancy | 09-Feb-89 | 2 | |
3 | Ms. | Greenboro | Lottie | R | 02-Oct-81 | 4 |
4 | Mrs. | Rumpersfro | Jennie | S | 01-Jun-71 | 5 |
5 | Mr. | Smith | Robert | L | 23-Nov-59 | 3 |
6 | Mr. | Renselear | Cary | A | 25-Dec-85 | 1 |
7 | Mr. | Ogallo | Roberto | S | 31-Jul-82 | 3 |
8 | Ms. | Johnson | Elizabeth | I | 10-Sep-88 | 1 |
9 | Mr. | Eindsmar | Jack | W | 19-Apr-55 | 2 |
10 | Mrs. | Jones | Rose | R | 06-Mar-66 | 4 |
11 | Mr. | Broderick | Tom | 21-Oct-72 | 3 | |
12 | Mr. | Washington | Alan | Y | 08-Sept-74 | 2 |
13 | Mr. | Smith | Peter | N | 25-Aug-64 | 3 |
14 | Ms. | Smith | Sherry | H | 25-May-66 | 4 |
15 | Mr. | Olenko | Howard | U | 24-May-64 | 5 |
16 | Mr. | Archialo | Barry | V | 03-Sep-60 | 5 |
17 | Ms. | Grimaldo | Jeanine | K | 12-Nov-70 | 4 |
18 | Mr. | Rosenburg | Andrew | D | 24-Jan-71 | 4 |
19 | Mr. | Rosten | Peter | F | 03-Oct-68 | 4 |
20 | Mr. | Mckee | Robert | S | 06-Mar-70 | 1 |
21 | Ms. | Baumann | Jennifer | A | 11-Dec-74 | 3 |
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 |
Trending nowThis is a popular solution!
Chapter 3 Solutions
Database Systems: Design, Implementation, & Management
- Modify the CHARTER table to add the attributes shown in the following table. Use the following precision for each row:arrow_forwardDisplay the salesperson name, product finish, and total quantity sold (label as TotSales) for each finish by each salesperson.arrow_forwardExamine this product table's column definitions:pid number primary keypname varchar2(50)You must display column headings as shown:Product_ID Product NameWhich SELECT statement does this? SELECT pid Product_ID, pname Product Name FROM product; SELECT pid Product_ID, pname "Product Name" FROM product; SELECT pid @Product_ID, pname @"Product Name" FROM product; SELECT pid AS Product_ID, pname AS Product Name FROM productarrow_forward
- Break the list in Figure 1-31 into tables, each with a single theme. Create ID columns as you think necessary.arrow_forwardPlease produce a set of nomalized relations from the following table: INVOICE Invoice Customer Customer Customer Customer Product Phone # Style Invoice Date ID # Name Address Product Unit Product Product Invoice VAT Total Description Price ID # Quantity Totalarrow_forwardCreate a list showing only the EmployeeID, Sales, and Projected Sales sorted from highest to lowest amount for Salesarrow_forward
- To create a student table with the student id, class, section, gender, name, dob, and marks as attributes where the student id is the primary key.arrow_forwardNormalize the following form into UNF 1NF, 2NF and 3NF. Show primary keys and foreign keys for all the normalized tables (from 3NF). Employee Task Record Form EmployeelD : 587 Employee Name: Smith Department ID: D1 DeparmentName : IT Task ID Task Name Duration Supervisor ID | Supervisor Name Emp_TaskJoin_Date Designing Drawing Reporting T1 70hrs S1 Khalfan 12/12/2019 T2 56hrs S2 Fatma 01/10/2020 T3 60hrs S3 Haitham 11/11/2020arrow_forwardBased on the ERD below, display all fields in the JOB table sorted by the largest hourly charges for the job first.arrow_forward
- Display the CID, CName, RID and RType from both tables using the NATURAL JOIN clause.arrow_forwardCreate correlated and noncorrelated subqueries and understand when to use.arrow_forward5. In the OrderItems table, display a count of the number of products where item_price is greater than or equal to $6, grouped by prod_id, but only for those groups having 3 or more of a given prod_id. Display the count with the alias shown. Sort by prod_id in ascending order. BRO3 1 rows returned in 0.03 seconds PROD ID Download 4 NUM PROD IDarrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrNp Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage