See Description
March 3rd, 2022
Assignment #1
Instruction for properly completing the assignment:
Due Sunday of Week 1 by 11:55 PM [5% of your grade]
- Download the attached template called “INFO620-Assignment1_LastNameFirst.doc”.
- Change the file name applying your Last Name and First Name in place of the LastNameFirst.
- Then open the newly saved file and enter your name and the date due on the top of the document.
- Then enter each problem’s solution within this same file in their proper locations WITHOUT changing or erasing the questions.
- Support your responses and answers by references from the text.
- Review and complete all questions.
- Then upload to Assignment #1 area of the Assignments.
- Your grade may be reduced if these instructions are NOT followed closely.
Week 1 Assignment Grading Rubric:
|
#1.9 |
0.2 points |
#3.13 |
0.3 points |
#4.12a |
0.2 points |
|
#1.10 |
0.2 points |
#3.16 |
0.3 points |
#4.12b |
0.2 points |
|
#1.12 |
0.2 points |
#3.19a |
0.2 points |
#4.12c |
0.2 points |
|
#1.13 |
0.2 points |
#3.19b |
0.2 points |
#4.12d |
0.2 points |
|
#1.14a |
0.2 points |
#3.19c |
0.2 points |
#4.12e |
0.2 points |
|
#1.14b |
0.2 points |
#3.19d |
0.2 points |
#4.12f |
0.2 points |
|
#2.14 |
0.3 points |
#3.20 |
0.3 points |
#4.15a |
0.2 points |
|
#2.15 |
0.3 points |
|
|
#4.14b |
0.1 points |
Here are the contents of Assignment #1 (however – please use the template attached – don’t copy and paste from here):
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. Reference the entire problem in the text
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?
