1. Development of physical Database design.
2. Understanding of DDL and DML SQL commands.
3. Understanding of mapping of logical design to physical design.
4. Functions in SQL
1. You are required to read roles & responsibilities of each entity mentioned in previous CS403_spring2009_module2.
2. You are allowed to consult the online material
3. Do not copy and paste other wise your case shall be submitted to unfair means committee. Copy and pasted material from any source will be awarded zero marks.
4. Please use a 12-point font in Times New Roman or Arial.
5. The Assignment includes Lecture # 21 to Lecture #31.
6. Last date for the Submission of the assignment is June 5, 2009.
7. Upload your assignment in .doc format (NOT in .docx & database file) and within due date and time. No assignment will be accepted after the due date and time.
8. You can use MS SQL server (any version) as DBMS software for execution of Queries and functions.
Consider the following few relations of Academic systems.
1. Semester (SemNo, Sem_Dur, Start_Date, End_Date)
2. Professor (VUPid, P_Name, Age, Deptid, CourseCode)
3. Prof_Exp(VUPid, Experience)
4. Department (Deptid, D_Name, Main_Office, HOD)
5. Project (Pid, Title, Start_Date, Due_Date, Prj_Instructor)
6. CoursePre-Req(CourseCode, Pre-requisite)
7. Course (CourseCode, C_Name, Prg_Code)
8. Student (VUSid, Std_Name, Std_Age, Prg_Code, )
9. Program (Prg_Code, Prg_Name, No_of_Sem)
10. ProgramDur (Prg_Code , Max_Dur)
Write down the QUERIES for the following given situations.
a. Create a new data base. Name it VUDBMS.
b. Create Tables with attributes and name them accordingly.
c. Insert data in related Tables (program, student, professor, degree).
***. Show one command for single entry in each relation.
d. List the names, ages, Department Name of professors, working in a different department.
e. List the professor Names, Total Experience and Group by professor Names.( using aggregate functions)
f. List the program code, program Name, Max. Duration from relations named Program. ( using aggregate functions)
g. List Department Name, Address and HOD Name.
h. List student ID, Student Name, student age where student ages are between 18 and 25 and sort by age field in ascending order.
i. Alter student relation by adding column Address.
j. List project title, start date and project instructor where project due date is greater then today date and sort result by start date field in Ascending order (using aggregate function getdate())
Hint (for b):
Specify the column data type. System or user-defined data types are acceptable. User-defined data types are created with sp_addtype before they can be used in a table definition. The NULL/NOT NULL assignment for a user-defined data type can be overridden during the CREATE TABLE statement. However, the length specification cannot be changed; you cannot specify a length for a user-defined data type in a CREATE TABLE statement.
Copy form previous Module:
Following are the roles and responsibilities of each of the given entities.
A. Withdrawal from course (s): A student shall not be allowed to add a new course, or substitute a course for another after the expiry of ten days from the commencement of the semester.
B. No student will take any course unless he/she has cleared the pre-requisite for it as determined by the University.
C. Professors can teach the same course in several semesters in different programs. Like CS201 is offering in 1st and 2nd semesters of Master and BS programs respectively.
D. Every professor must teach some courses.
E. Every professor teaches exactly one course (no more, no less), and every course must be taught by some professor.
F. Now suppose that certain courses can be taught by a team of professors jointly(MTH603 by Dr. Junaid Zaidi & Dr. Atta-ullah-khan), but it is possible that no one professor in a team can teach the course. Model this situation, introducing additional entity sets and relationship sets if necessary.
G. Professors have an Vupid, a name, an age, a rank, experiences and a research specialty.
H. Graduate students have an Vusid , a name, an age, and a degree program (e.g., M.S. or BS honors, etc).
I. Student can enroll in the same course in several semesters, and only the most recent such offering needs to be recorded. ( mostly incase of grade improvement)
J. Graduate students have one major department in which they are working on their degree.
K. Course projects have a project number, project title, a starting date, a Due date.
L. Each project is managed by one professor (known as the project instructor).
M. Each project is worked on by one or more professors (known as the project's Co-instructors).
N. Professors can manage and/or work on multiple projects.
O. Each project is worked on by one or more graduate students
P. Departments have a department number (deptid) , a department name, Contact no. and a main office
Q. Departments have a professor (known as the HOD **) who runs the department.
For better understanding about roles & responsibilities of course, program, semester entities you can visit: http://www.vu.edu.pk/pages/statutes.aspx
1. There are two semesters* in each academic year, Fall and Spring, of 18 weeks each.
2. VU is offering following programs.
2-yr Bachelors Programs
Certificate Courses MS
3. VU has following Academic departments:
Computer Science Department
Kindly do follow the blog and the solution is coming soon.
* “Semester” means teaching time of 18 weeks inclusive of admission, conduct of examination, preparation and declaration of result etc. and gazetted holidays
**. HOD stands for Head of Department.
Here is the solution in the form of Picture do it by your self adding the commands to SQL.Follow these pics one by one
Assignment # 4 Kindly send email to VUHELPS for the urgent reply.