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 |
Trending nowThis is a popular solution!
Chapter 3 Solutions
Database Systems: Design, Implementation, & Management
- 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_forwardWhat is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?arrow_forwardWhat three join types are included in the outer join classification?arrow_forward
- convert it to relations that are normalizationarrow_forwardCreate 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_forwardPlease look at the schemas carefully (they is no keyword called manufacturers). The answer should be in 1 combined relational algebra expression. Join Printer and Product on a shared modelarrow_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_forwardWrite only the relational schema for 1st and 2nd Normal Forms based on the following records.arrow_forward
- COMPREHENSIVE MICROSOFT OFFICE 365 EXCEComputer ScienceISBN:9780357392676Author:FREUND, StevenPublisher:CENGAGE LDatabase 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 LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning