Candidate key vs Primary key in a relational schema

Semantic Integrity and Semantic constraints are also covered

Primary Keys

In a practical relational database, each relation schema must have a primary key.

Rules for primary keys:

  • 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.

We will be working on a relational database schema called Car dealership.The diagram below show the schema diagram for the Car Dealership relational database schema.

A database has to be designed to keep track of automobile sales in a car dealership.Please answer the following questions based on the above schema.

Question 1: Identify the primary key of the relation Car.

Question 2: Identify the foreign key of the relation Sale.

Question 3: How many constraint types are there in relation Sale?

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 Constraints

Domain constraints specify that within a tuple the value of each attribute must be an element from the domain of that attribute. The data types associated with the domains include:

  • 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

Other possible domain values may be a sub-range of values from a data type or as an enumerated data type in which values are explicitly listed.

--

--

Passionate author, strategic investor, financial advisor

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