CREATE Table

Creating the database StudentAttendance gives an empty container; the real design work is defining relations (creating tables) inside it. For each relation you must specify its attributes, give each attribute a data type, and decide the restrictions its values must obey. All of this is expressed through the CREATE TABLE statement.

The syntax.

CREATE TABLE tablename(
   attributename1 datatype constraint,
   attributename2 datatype constraint,
   :
   attributenameN datatype constraint);

Four points about this statement deserve attention:

• N is the degree of the relation — the table has N columns.
• Each attribute name specifies the name of a column in the table.
• The datatype specifies the type of data that the attribute can hold.
• The constraint indicates the restrictions imposed on the values of an attribute. By default, every attribute can take NULL values — except the primary key, which cannot.

Choosing data types is a reasoning exercise, not guesswork. Consider the attributes of the STUDENT relation. Assuming a class has at most 100 students, with roll numbers running in sequence from 1 to 100, three digits are sufficient for RollNumber — so the numeric type INT is appropriate. Student names vary in length; assuming a name never exceeds 20 characters, SName is given VARCHAR(20). A date of birth is naturally of type DATE. And supposing the school uses the guardian's 12-digit Aadhaar number as GUID, that attribute is declared CHAR(12): the Aadhaar number has a fixed length, and no mathematical operation will ever be performed on it, so there is no reason to store it as a number.

Two questions guide every such decision: is the length fixed (CHAR) or variable (VARCHAR), and will arithmetic ever be done on the value (numeric type) or not (character type)?

The three relations of StudentAttendance. The chosen data type and constraint for every attribute are:

STUDENT (Table 8.3)

GUARDIAN (Table 8.4)

ATTENDANCE (Table 8.5)

The asterisk means part of a composite primary key: in ATTENDANCE, AttendanceDate and RollNumber together form the primary key. …