Enterprise Database Systems Assignments Page 1

Info620 Enterprise Database Systems Assignments Page 1

Cleaned assignment instructions:

Week 1 – Assignment #1: Chapters 1-4

Answer the following questions based on chapters 1 through 4 of the textbook:

  1. Explain the difference between controlled and uncontrolled redundancy.
  2. Specify all the relationships among the records of the database shown in Figure 1.2.
  3. Cite some examples of integrity constraints that can apply to the database shown in Figure 1.2.
  4. Give examples of systems where using traditional file processing might make sense instead of a database approach.
  5. Considering Figure 1.2:
    • a. If the name of the ‘CS’ (Computer Science) Department changes to ‘CSSE’ (Computer Science and Software Engineering) and the course number prefix also changes, identify the columns that need updating.
    • b. Restructure the columns in COURSE, SECTION, and PREREQUISITE tables so that only one column needs to be updated when such a change occurs.
  6. For Chapter 2:
    • a. If designing a web-based airline reservation system, which DBMS architecture would you choose and why? Why are other architectures less suitable?
    • b. In Figure 2.1, identify columns in tables that must have unique values across all rows, in addition to value constraints between tables.
  7. For Chapter 3:
    • a. For the relation CLASS(Course#, Univ_Section#, InstructorName, Semester, BuildingCode, Room#, TimePeriod, Weekdays, CreditHours), suggest candidate keys.
    • b. Given relations STUDENT, COURSE, ENROLL, BOOK_ADOPTION, and TEXT, specify foreign keys, stating any assumptions.
    • c. For a STUDENT relation with attributes (Name, SSN, Local_phone, Address, Cell_phone, Age, GPA), identify missing information for phone attributes, discuss storing additional info, and analyze splitting the Name attribute into multiple fields. Provide general guidelines for attribute design choices.
    • d. Considering privacy laws disallowing SSN as a primary key, discuss alternative key usage such as StudentID.
  8. For Chapter 4:
    • a. Write SQL queries based on Figure 1.2:
      • i. Names of senior students majoring in 'COSC'
      • ii. Courses taught by Professor King in 85 and 86
      • iii. For each section taught by King, course number, semester, year, and number of students
      • iv. Name and transcript of each senior majoring in COSC
      • v. Names and majors of students with all A grades
      • vi. Names and majors of students with no A grades
    • b. For the EMPLOYEE table, with altered foreign key constraints (ON DELETE CASCADE, ON UPDATE CASCADE), explain:
      • i. What happens when deleting an employee where last name is ‘Borg’
      • ii. Which cascade option (CASCADE or SET NULL) is preferable for the EMPSUPERFK constraint and why

Paper For Above instruction

The assignment for Week 1 of the Enterprise Database Systems course spans Chapters 1 through 4, encompassing fundamental concepts of database design, architecture, constraints, SQL querying, and data integrity. This comprehensive set of questions aims to evaluate understanding of core database principles, relational models, schema design, and the practical implications of constraints and security considerations in enterprise environments.

Differences Between Controlled and Uncontrolled Redundancy

Controlled redundancy involves deliberate duplication of data within a database to enhance performance, availability, or reliability, managed carefully through database constraints and normalization techniques (Elmasri & Navathe, 2015). Examples include denormalized schemas where certain data is duplicated for quicker retrieval. In contrast, uncontrolled redundancy is unintentional, often resulting from poor database design, leading to anomalies, inconsistent data, and maintenance difficulties (Date, 2012). For instance, duplicate customer entries across tables due to lack of constraints exemplify uncontrolled redundancy, causing data integrity issues.

Relationships Among Records in Figure 1.2

Although Figure 1.2 is hypothetical here, typical relationships in such a database might include one-to-many relationships (e.g., one department to many courses), many-to-many relationships (e.g., students to courses via enrollment), and one-to-one relationships (e.g., student profiles linked to login credentials). Proper foreign keys and referential integrity constraints ensure these relationships maintain consistency (Hoffer et al., 2016).

Integrity Constraints for the Database

Integrity constraints are rules ensuring data correctness and reliability. Examples include primary key constraints to guarantee entity integrity, foreign key constraints to maintain referential integrity, and domain constraints restricting data types and formats (Elmasri & Navathe, 2015). For the database in question, constraints might involve ensuring that course numbers follow a specific pattern, student IDs are unique, and that semester data adheres to valid year and term values.

Systems Suitable for Traditional File Processing

Traditional file processing may be appropriate in simple, one-time applications with minimal data processing needs, such as small-scale inventory management or logging systems where data volume and complexity are low, and updates are infrequent (Date, 2012). These systems lack the overhead of database management systems and are suitable when data consistency, concurrency, and complex querying are non-issues.

Database Column Updates and Restructuring for Flexibility

When the ‘CS’ department name changes to ‘CSSE’, columns such as DepartmentName in Department, CoursePrefix in Course, or related fields would need updating. To minimize updates, restructuring can involve integrating attributes into master tables or employing join tables. For example, normalizing department info into a separate Department entity and referencing it via foreign keys allows updating the department name in a single place, affecting all related records automatically (Hoffer et al., 2016).

DBMS Architecture for Web-Based Airline Reservations

A three-tier client-server architecture is suitable here for scalability, security, and manageability (Elmasri & Navathe, 2015). This architecture separates the user interface, application logic, and database, facilitating distributed processing and web access. Other architectures like centralized or monolithic systems may lack scalability and flexibility, making them less suitable for large-scale web applications where multiple users access and modify data simultaneously.

Unique Columns in Tables

In addition to primary keys, other columns must have unique constraints—such as StudentNumber in a STUDENT table, CourseCode in COURSE, and Book_ISBN in TEXT—to prevent duplication and ensure data integrity across the database (Hoffer et al., 2016).

Candidate Keys and Foreign Keys

For the CLASS relation, candidate keys include (Course#, Univ_Section#) since Univ_Section# uniquely identifies each class offering. Other potential candidates may include composite keys involving instructor or semester details if they are unique, but typically Course# and Univ_Section# suffice. For STUDENT, SSN is a primary key; in other relations like ENROLL, foreign keys include StudentID referencing STUDENT and Course# referencing COURSE. Assumptions include that each course is uniquely identified by Course# and each enrollment links a student to a course offering.

Design Considerations for Student Relations

Missing information in local and cell phone attributes includes country code and area code to handle international calls. Storing this in existing fields may lead to inconsistencies; providing separate fields such as CountryCode and AreaCode enhances clarity and data integrity (Elmasri & Navathe, 2015). Splitting Name into first, middle, and last names improves data usability for sorting and personalization but increases complexity. A guideline is to split attributes when the stored data benefits from functional separation, especially for retrieval or display purposes.

Primary Key Alternatives under Privacy Laws

Institutions now prefer using StudentID as the primary key rather than SSN for privacy compliance, ensuring unique identification without exposing sensitive data. This shift enhances security while maintaining ability to uniquely associate student records across systems (Elmasri & Navathe, 2015).

SQL Queries for the Database

a) Retrieve names of all senior students majoring in 'COSC':

SELECT Name FROM STUDENT WHERE Major='COSC' AND Class=5;

b) Retrieve course names taught by Professor King in 1985 and 1986:

SELECT CourseName FROM COURSE WHERE Instructor='King' AND Year IN (1985, 1986);

c) For each section taught by Professor King, retrieve course number, semester, year, and student count:

SELECT Univ_Section#, Semester, Year, COUNT(*) AS StudentCount

FROM CLASS


WHERE InstructorName='King'


GROUP BY Univ_Section#, Semester, Year;

d) Name and transcript of each senior (Class=5) majoring in COSC, including courses and grades:

SELECT S.Name, C.CourseName, C.CourseNumber, C.CreditHours, E.Semester, E.Year, E.Grade

FROM STUDENT S


JOIN ENROLL E ON S.SSN = E.SSN


JOIN COURSE C ON E.CourseID = C.CourseID


WHERE S.Major='COSC' AND S.Class=5;

e) Names and majors of students with all A grades:

SELECT DISTINCT S.Name, S.Major

FROM STUDENT S


WHERE NOT EXISTS (


SELECT 1 FROM ENROLL E


WHERE E.SSN = S.SSN AND E.Grade  'A'


);

f) Names and majors of students with no A grades:

SELECT DISTINCT S.Name, S.Major

FROM STUDENT S


WHERE NOT EXISTS (


SELECT 1 FROM ENROLL E


WHERE E.SSN = S.SSN AND E.Grade = 'A'


);

Regarding the Employee table constraint change to ON DELETE CASCADE and ON UPDATE CASCADE, deleting an employee with last name ‘Borg’ will automatically delete their subordinate employee records (if linked through the SUPERSSN foreign key). Under these cascade rules, related records are removed or updated accordingly, maintaining referential integrity (Hoffer et al., 2016). Whether cascade or set null is preferable depends on organizational policy; cascade removes dependent records, which might lead to unintended data loss, while SET NULL retains records but breaks the reference.

References

  • Date, C. J. (2012). Database design and relational theory: Normal forms and keys. O'Reilly Media.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Connolly, T., & Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.
  • Database System Concepts (6th ed.). McGraw-Hill.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Hoper, J. A., Venkataraman, R., & Topi, H. (2019). Modern Database Management. Pearson.
  • Pratt, P. J., & Adamski, J. (2016). Concepts of Database Management. Cengage Learning.
  • McGovern, T., et al. (2018). Database Design and Implementation. Morgan Kaufmann.
  • Korth, H. F., & Silberschatz, A. (2010). Database System Concepts. McGraw-Hill Education.

Related Assignments