Candidate key vs Primary key in a relational schema

Primary Keys

If a relation schema has more than one key, then each key is called a candidate key.One of the candidate keys is designated as the primary key, and the others are called secondary keys.

Rules for primary keys:

The value of the Primary Key must be unique for each instance of the entity.

  • There can be no missing values( ie. Not Null) for Primary Keys. If the Primary Key is composed of multiple attributes, each of those attributes must have a value for each instance.
  • The Primary Key is immutable.i.e., once created the value of the Primary Key cannot be changed.
  • If the Primary Key consists of multiple attributes, none of these values can be updated.
The solutions to the assignment are given below:
Question 1: Identify the primary key of the relation Car.Answer: Serial noQuestion 2: Identify the foreign key of the relation Sale.Answer: Serial noQuestion 3: How many constraint types are there in relation Sale?Answer: 2 constraints : Primary Key and Foreign Key

Semantic Integrity

Semantic integrity ensures that data entered into a row reflects an allowable value for that row. The value must be within the domain, or allowable set of values, for that column. For example, the quantity column of the items table permits only numbers. If a value outside the domain can be entered into a column, the semantic integrity of the data is violated.

Semantic Constraints

Semantic Constraints are constraints that cannot be directly expressed in the schemas of the data model. Semantic constraints are also called application-based rules or business rules. They are additional rules specified by users or database administrators. For example, a class can have a maximum of 30 students; salary of an employee cannot exceed the salary of the employee’s manager.

  • Integers (short integer, integer, long integer)
  • Real numbers (float and double precision float)
  • Characters
  • Booleans
  • Fixed-length strings and variable length strings
  • Date, time, timestamp
  • Money
  • Other special data types

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Aditya B

Aditya B

3 Followers

Passionate author, strategic investor, financial advisor