DataBase managment design.

 

Do the dowsings:

1. Run the sql commands written in ddl.sql file attached

2. Create_a dummy data. You can use "fake data generators"

3. Write followings

- Write the query that gets all the students with their grades which take for example MAT 101.

- Write the query that gets all lectures with their grades which the student with id for example 1

 

 

 

 

Transcribed Image Text:

create table students ( ); student_id serial primary key, citizen_number char (11) unique not null, first name varchar(100) not null, middle name varchar(100), last name varchar(100) not null, birth date date not null, address varchar (1024) not null create table lectures ( lecture_code char (7) primary key, name varchar(100) not null, credits int ); create table grades ( description ); grade_id serial primary key, char (2) not null, value double precision not null insert into grades (description, value). values ('AA', 4.0), ('BA, 3.5), ('BB', 3.0), ('CB', 2.5), ('CC', 2.0), ('DC, 1.5), ('DD', 1.0), ('FD', 0.5), ('FF', 0.0), ('NA', -1), ('P', -1); create table enrolls ( enroll_id serial primary key, student_id int references students (student_id) not null, lecture_code char (7) references lectures (lecture_code) not null, grade_id int references grades (grade_id) ) create table students ( ); student_id serial primary key, citizen_number char (11) unique not null, first name varchar(100) not null, middle name varchar(100), last name varchar(100) not null, birth date date not null, address varchar (1024) not null create table lectures ( lecture_code char (7) primary key, name varchar(100) not null, credits int ); create table grades ( description ); grade_id serial primary key, char (2) not null, value double precision not null insert into grades (description, value). values ('AA', 4.0), ('BA, 3.5), ('BB', 3.0), ('CB', 2.5), ('CC', 2.0), ('DC, 1.5), ('DD', 1.0), ('FD', 0.5), ('FF', 0.0), ('NA', -1), ('P', -1); create table enrolls ( enroll_id serial primary key, student_id int references students (student_id) not null, lecture_code char (7) references lectures (lecture_code) not null, grade_id int references grades (grade_id) )