Retrieve the names of all senior students majoring in cs

INFO620: Enterprise Database Systems – Assignments Page | 1

Name: _________________________________________ Date: __________________

Week 1 – Assignment #1: Chapters 1-4 Grade: 5% of Class Grade

CH1: DATABASES AND DATABASE USERS

#1.9 - What is the difference between controlled and uncontrolled redundancy?

#1.10 - Specify all the relationships among the records of the database shown in Figure 1.2.

#1.12 – Cite some examples of integrity constraints that you think can apply to the database shown in Figure 1.2.

#1.13 - Give examples of systems in which it may make sense to use traditional file processing instead of a database approach.

#1.14 - Consider Figure 1.2.

a. If the name of the ‘CS’ (Computer Science) Department changes to ‘CSSE’ (Computer Science and Software Engineering) Department and the corresponding prefix for the course number also changes, identify the columns in the database that would need to be updated.

b. Can you restructure the columns in COURSE, SECTION, and PREREQUISITE tables so that only one column will need to be updated?

CH 2: DATABASE SYSTEM CONCEPTS AND ARCHITECTURE

#2.14 - if you were designing a Web-based system to make airline reservations and to sell airline tickets, which DBMS Architecture would you choose from Section 2.5? Why? Why would the other architectures not be a good choice?

#2.15 - Consider Figure 2.1. In addition to constraints relating the values of columns in one table to columns in another table, there are also constraints that impose restrictions on values in a column or a combination of columns within a table. One such constraint forces that a column or a group of columns must be unique across all rows in the table. For example, in the STUDENT table, the StudentNumber column must be unique (to prevent two different students from having the same StudentNumber). Identify the column or the group of columns in the other tables that must be unique across all rows in the table?

CH 3: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE CONSTRAINTS

#3.13 - Consider the relation CLASS(Course#, Univ_Section#, InstructorName, Semester, BuildingCode, Room#, TimePeriod, Weekdays, CreditHours). This represents classes taught in a university with unique Univ_Section#. Give what you think should be various candidate keys and

#3.16 - Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course:

STUDENT (SSN, Name, Major, Bdate)

COURSE (Course#, Quarter, Grade)

ENROLL (SSN, Course#, Quarter, Grade)

BOOK_ADOPTION (Course#, Quarter, Book_ISBN)

TEXT (Book_ISBN, Book_Title, Publisher, Author)

Specify the foreign keys for this schema, stating any assumptions you make.

#3.19 - Consider a STUDENT relation in a UNIVERSITY database with the following attributes (Name, SSN, Local_phone, Address, Cell_phone, Age, GPA). Note that the cell phone may be from a different city and state (or province) from the local phone. A possible tuple of the relation is shown below:

Name

SSN

LocalPhone

Address

CellPhone

Age

GPA

George Shaw William Edwards

123-45-6789

555-1234

123 Main St., Anytown, CA 94539

555-4321

19

3.75

a. Identify the critical missing information from the LocalPhone and CellPhone attributes as shown in the example above. (Hint: How do call someone who lives in a different state or province?)

b. Would you store this additional information in the LocalPhone and CellPhone attributes or add new attributes to the schema for STUDENT?

c. Consider the Name attribute. What are the advantages and disadvantages of splitting this field from one attribute into three attributes (first name, middle name, and last name)?

d. What general guideline would you recommend for deciding when to store information in a single attribute and when to split the information.

#3.20 - Recent changes in privacy laws have disallowed organizations from using SSN to identify individuals unless certain restrictions are satisfied. As a result, most US universities cannot use SSNs as primary keys (except for financial data). In practice, StudentID, a unique ID, a unique identifier, assigned to every student, is likely to be used as the primary key rather than SSN since StudentID is usable across all aspects of the system.

CH 4: Basic SQL

#4.12 - Specify the following queries in SQL on the database schema of Figure 1.2.

a) Retrieve the names of all senior students majoring in 'COSC' (computer science).

b) Retrieve the names of all courses taught by professor King in 85 and 86.

c) For each section taught by professor King, retrieve the course number, semester, year, and number of students who took the section.

d) Retrieve the name and transcript of each senior student (Class=5) majoring in COSC. Transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student.

e) Retrieve the names and major departments of all straight A students (students who have a grade of A in all their courses).

f) Retrieve the names and major departments of all students who do not have any grade of A in any of their courses.

#4.15 - Consider the EMPLOYEE table’s constraint EMPSUPERFK as specified in Figure 4.2 is changed to read as follows:

CONSTRAINT EMPSUPERFK

FOREIGN KEY (SUPERSSN) REFERNCES EMPLOYEE(SSN)

ON DELETE CASCADE ON UPDATE CASCADE,

Answer the following questions:

a. What happens when the following command is run on the database state shown in Figure 5.6?

DELETE EMPLOYEE WHERE LNAME = ‘Borg’

b. Is it better to CASCADE or SET NULL in case of EMPSUPERFK constraint ON DELETE?

American Public University System Kageorgis