DBMS-Assignment 1

Database Management Systems

Assignment 1.

To create simple tables with only the primary key constraint ( as a table level constraint & as a field level constraint) (include all data types) 

A table is a database object that holds data. A table must have unique name, via which it can be referred. A table is made up of columns. Each column in the table must be given a unique name within that table. Each column will also have size a data-type and an optional constraint. The data types permitted are as follows:

a. Character data types:

1 char(n): It is fixed length character string of size n, default value 1 byte if n is not specified.

2 varchar(n): It is variable length character string with maximum size n.

3 text: It is used to store large text data, no need to define a maximum.

b. Numeric data types:

1 Integer, int , serial: Serial is same as int, only that values are incremented automatically.

2 Number: A real number with P digits, S of themafter decimal point. 

3 Float: Real number.

c. Date and time type:

1 Date: Stores date information.

2 Time: Stores time information.

3 Timestamp: Stores a date & time

d. Boolean and Binary type:

1 Boolean, bool: Stores only 2 values : true or false, 1 or 0, yes or no, y or n, t or f.

There are two constraints applied at the time of creation of table. They can be defined as either of the following :

1. Column level: When data constraint is defined only with respect to one column & hence defined after the column definition, it is called as column level constraint.

Syntax: Create tablename (attribute1 datatype primary key, attribute2 datatype , constraint constraint-name ,……)

2. Table Level: When data constraint spans across multiple columns & hence defined after defining all the

table columns when creating or altering a table structure, it is called as table level constraint.

Syntax: Create tablename ( attribute1 datatype , attribute2 datatype2 ,……, constraint pkey primary key(attribute1))

Syntax for table creation :

Create tablename ( attribute list);

Attribute list : ( [ attribute name data type optional constraint] ,................)

Primary key concept : A primary key is made up of one or more columns in a table, that uniquely identify each row in the table. A column or a set of columns defined as a primary key, must conformto the following properties

a) The column/s cannot have NULL values.

b) The data held across the column/s MUST be UNIQUE. 

Syntax:

1. Create tablename ( attribute1 datatype primary key , attribute2 datatype ,……) 

2. Create tablename ( attribute1 datatype , attribute2 datatype ,……, constraint pkey primary key(attribute1)) 

3. Create tablename ( attribute1 datatype, attribute2 datatype ,……, constraint constraint_name primarykey(attribute1,attribute2))

Assignment:- 

Set A 

Create table for the information given below by choosing appropriate data types and also specifying proper primary key constraint on fields which are underlined

 1. Player (player_id int, name varchar(50), Birth_date date ,Birth_place varchar(100))

Query:- Create table Player(player_id int primary key, name varchar(50), Birth_date date ,Birth_place varchar(100);

2. Student (roll_no int, class varchar(20), weight numeric(6,2),height numeric(6,2)) 

Query:- create table Student (roll_no int , class varchar(20), weight numeric(6,2),height numeric(6,2) , PRIMARY KEY (roll_no,class));

3 .Project (project_id int,project_name varchar(20), project_description text,status boolean)

Query:- create table project(project_id int primary key ,project_name varchar(20), project_description text,status boolean);

 4. Donor (donor_no,donor_name,blood_group,last_date) 

Query:- create table donor( donor_no int primary key,donor_name  varchar(50), blood_group char(3), last_date date);

5. Movie(movie-no, name, release-year )

Query:-  create table movie(movie_no int primary key, name varchar(50), release_year varchar(20));

Set B 

Create table for the information given below by choosing appropriate data types and also specifying proper primary key constraint on fields which are underlined

 1. Teacher ( Teacher_no, Tname,qualification,address) 

Primary key : Teacher_no 

Query:- create table Teacher(teacher_no int primary key, tname varchar(50), qualification varchar(50), address text);

 

2. Driver (Driver_no,permit_no,Dname,address) 

Primary key: Driver_no,permit_no 

Query:- create table Driver( driver_no int , permit_no int, dname varchar(50), address text, PRIMARY KEY( driver_no,permit_no));