Scribd
Upload
681 views

Access

Here are the SQL queries to answer the questions: a) What are the courses included in the Section table? List each course only once SELECT DISTINCT Course.Name FROM Section INNER JOIN Course ON Section.CourseID = Course.ID; b) List all students in alphabetical order by StudentName. SELECT StudentName FROM Student ORDER BY StudentName; c) List the students who are enrolled in each course in semester 1/2008. Group the students by the sections in which they are enrolled. SELECT Section.CourseID, Section.SectionNo, Student.StudentName FROM Section INNER JOIN Registration ON Section.SectionNo = Registration.SectionNo INN

Uploaded by

lomalohi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
681 views

Access

Here are the SQL queries to answer the questions: a) What are the courses included in the Section table? List each course only once SELECT DISTINCT Course.Name FROM Section INNER JOIN Course ON Section.CourseID = Course.ID; b) List all students in alphabetical order by StudentName. SELECT StudentName FROM Student ORDER BY StudentName; c) List the students who are enrolled in each course in semester 1/2008. Group the students by the sections in which they are enrolled. SELECT Section.CourseID, Section.SectionNo, Student.StudentName FROM Section INNER JOIN Registration ON Section.SectionNo = Registration.SectionNo INN

Uploaded by

lomalohi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 8

Q1: Our Friend Los Gatos realizing that his wallcovering business had a few wrinkles

in it, decide to pursue a law degree at night. After graduating, he has teamed up
with Lyla El Pajaro to form Peck and Paw, Attorneys at Law. Los and Lyla have hired
you to design a database system based upon the following set of business rules. It
is in your best interest to perform a though analysis, to avoid needless litigation.
Please create an ERD based upon the following set of rules:

An ATTORNEY is retained by one or more CLIENTS for each CASE.


Attributes for ATTORNEY are Attorney ID, Name Address, City, State, Zip
Code, Specialty (may be more than one), and Bar (may be more than one).
A CLIENT may have more than one ATTORNEY for each CASE.
Attributes for CLIENT are Client ID, Name, Address, State, Zip Code,
Telephone, and Date of Birth.
A CLIENT may have more than one CASE.
Attributes for CASE are Case ID, Case Description, and Case Type.
An ATTORNEY may have more than one CASE.
Each CASE is assigned to one and only one COURT.
Attributes of COURT are Court ID, Court Name, City, State, and Zip Code.
Each COURT has one or more JUDGES assigned to it.
Attributes for JUDGE are Judge ID, Name, and Year In Practice.

Please state any assumptions that you have m

ade.
Also, draw a data model for this situation.

Q2: develop an EER model for the following situation.


An international school of technology has hired you to create a database
management system to assist in scheduling classes. After several interviews with

the president, you have come up with the following list of entities, attributes, and
initial business rules:

Room is identified by Building ID and Room No and also has a Capacity. A


room can be either a Lab or a Classroom. If it is a classroom, it has an
additional attribute called Board Type.
Media is identified by MType ID and has attributes of Media Type and Type
Description. Note: her we are taking type of media (such as a VCR, projector,
etc.), not the individual piece of equipment. Tracking of equipment is outside
of the scope of this project.
Computer is identified by CType ID and has attributes Computer Type, Type
Description, Disk Capacity, and Processor Speed. Please note: As with Media
Type, we are tracking only the type of computer, not an individual computer.
You can think of this as a class of computers (e.g., PIII 900MHZ).
Instructor has identifier Emp ID and has attributes Name, Rank, and Office
Phone.
Timeslot has identifier TSIS and has attributes Day of Week, Start Time, and
End Time.
Course has identifier Course ID and has attributes Course Description and
Credits. Course can have one, none, or many prerequisites. Courses also have
one or more sections.
Section has identifier Section ID and attributes Enrollment Limit.

After some further discussion, you have come up with some additional business
rules to help you create the initial design:

An instructor teaches one, none, ore many sections of a course in a given


semester.
An instructor specifies preferred time slots.
Scheduling data are kept for each semester, uniquely identified by semester
and year.
A room can be scheduled for one section or no section during one time slot in
a given semester of a given year. However, one room can participate in many
schedules, one schedule, or no schedules, or no schedules; one section can
participate in many schedules, one schedule, or no schedules.
A room can have one type of media, several types, or no media.
Instructors are trained to use one, none, or many types of media.
A lab has one or more computer types. However a classroom doesnt have
any computers.
A room cant be booth a classroom and a lab. There are also no other room
types to be incorporated into the system.

==============================================
===============================
Q5. Write SQL commands for the following:
a) Create two different forms of the INSERT command to add a student with a
student ID of 65798 and last name Lopez to the Student table.

SELECT Student.ID, Student.Name


FROM Student
WHERE (((Student.ID)=65798) AND ((Student.Name)="Lopez"));

b) A command that will remove Lopez from the Student table.

DELETE Student.ID, Student.Name


FROM Student
WHERE (((Student.Name)="Lopez"));
c) A command that will modify the name of course ISM 4212 from Database to
Introduction to Relational Database.

SELECT Course.ID, Course.Name


FROM Course
WHERE (((Course.ID)="ISM 4212"))
ORDER BY Course.ID;

Q6. Write SQL quarries to answer the following questions:


a) Which students have an ID number that is less than 50000?

SELECT Student.ID, Student.Name


FROM Student
WHERE (((Student.ID)<50000));
b) What is the name of the faculty member whos ID is 4756?

SELECT Faculty.ID, Faculty.Name


FROM Faculty
WHERE (((Faculty.ID)=4756));
c) What is the smallest section number used in the first semester of 2008?

SELECT Section.[No], Section.Semester


FROM [Section]
WHERE (((Section.[No])<=2712));

Q7. Write a SQL quarries to answer the following questions:


a) How many students are enrolled in section 2714 in the first semester of
2008?

SELECT Registration.StudentID, Registration.SectionNo


FROM Registration
WHERE (((Registration.SectionNo)=2714));
b) Which faculty members have qualified to teach a course since 1993? List the
faculty ID, course, and date of qualification.

SELECT Qualified.[Faculty ID], Qualified.CourseID,


Qualified.DateQualified
FROM Qualified
WHERE (((Qualified.DateQualified)>="9/1993"));
Or

SELECT Faculty.Name, Qualified.[Faculty ID], Qualified.CourseID,


Qualified.DateQualified
FROM Faculty, Qualified
WHERE (((Qualified.DateQualified)>="9/1993"));

Q8. Write a SQL quarries to answer the following questions:


a)
Which students are enrolled in Database and Networking? ( Hint: use
SectionNo for each class so you can determine the answer from the registration
table by itself.)

SELECT Course.ID, Section.[No], Registration.StudentID


FROM Course, [Section], Registration
WHERE (((Course.ID)="ISM 4212")) OR (((Course.ID)="ISM 4930"));

b. which instructors cannot teach both Syst Analysis and Syst Design?
dont know!

Q9. Write a SQL quarries to answer the following questions:

a)

what are the courses included in the Section table? List each course only

once

SELECT Section.CourseID, Course.Name


FROM [Section], Course;

b)

List all students in alphabetical order by StudentName.

SELECT Student.Name
FROM Student
ORDER BY Student.Name;

c) List the students who are enrolled in each course in semester 1/2008. Group
the students by the sections in which they are enrolled.
Dont know!

d) List the courses available. Group them by course prefix. (ISM is the only prefix
shown, but there are many others throughout the university.)
Dont know!

You might also like