Here are the deviation of Assignment #1 (however - fascinate use the template established - don't delineation and paste from short):
CH1: DATABASES AND DATABASE USERS
#1.9 - What is the distinction betwixt inferior and uninferior redundancy?
#1.10 - Specify all the connectionships discurrent the history of the factsbase shown in Figure 1.2.
#1.12 – Cite some developments of fullness absorptions that you judge can apportion to the factsbase shown in Figure 1.2.
#1.13 - Give developments of schemes in which it may form perception to use oral refine processing instead of a factsbase avenue.
#1.14 - Consider Figure 1.2.
a. If the wheedle of the ‘CS’ (Computer Science) Department changes to ‘CSSE’ (Computer Knowledge and Software Engineering) Department and the selfcorresponding preface for the route calculate to-boot changes, establish the supports in the factsbase that would scarcity to be updated.
b. Can you restructure the supports in COURSE, SECTION, and PREREQUISITE boards so that simply one support conciliate scarcity to be updated?
CH 2: DATABASE SYSTEM CONCEPTS AND ARCHITECTURE
#2.14 - if you were sharp a Web-based scheme to form airline reservations and to vend airline tickets, which DBMS Architecture would you cull from Minority 2.5? Why? Why would the other architectures not be a amiable exquisite?
#2.15 - Consider Figure 2.1. In individualization to absorptions of the values of supports in one board to supports in another board, tshort are to-boot absorptions that lay restrictions on values in a support or a synthesis of supports amid a board. One such absorption forces that a support or a cluster of supports must be singular athwart all rows in the board. For development, in the STUDENT board, the StudentNumber support must be singular (to bar two incongruous novices from having the identical StudentNumber). Identify the support or the cluster of supports in the other boards that must be singular athwart all rows in the board?
CH 3: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE CONSTRAINTS
#3.13 - Consider the connection CLASS(Course#, Univ_Section#, InstructorName, Semester, BuildingCode, Room#, TimePeriod, Weekdays, CreditHours). This represents classes taught in a university after a while singular Univ_Section#. Give what you judge should be sundry candicontinuance keys and
#3.16 - Consider the forthcoming connections for a factsbase that keeps footprint of novice enrollment in routes and the books adopted for each route:
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 outlandish keys for this schema, stating any assumptions you form.
#3.19 - Consider a STUDENT connection in a UNIVERSITY factsbase after a while the forthcoming propertys (Name, SSN, Local_phone, Address, Cell_phone, Age, GPA). Note that the cell phone may be from a incongruous city and particularize (or department) from the topical phone. A mitigated tuple of the connection is shown below:
George Shaw William Edwards
123 Main St., Anytown, CA 94539
a. Identify the important dropping advice from the LocalPhone and CellPhone propertys as shown in the development overhead. (Hint: How do wheedle someone who lives in a incongruous particularize or department?)
b. Would you treasury this individualizational advice in the LocalPhone and CellPhone propertys or add new propertys to the schema for STUDENT?
c. Consider the Wheedle property. What are the advantages and disadvantages of cleaveting this scope from one property into three propertys (chief wheedle, intermediate wheedle, and decisive wheedle)?
d. What open guideline would you praise for deciding when to treasury advice in a one property and when to cleave the advice.
#3.20 - Recent changes in concealment laws bear disallowed organizations from using SSN to establish men-folks spontaneous infallible restrictions are satisfied. As a issue, most US universities cannot use SSNs as original keys (save for financial facts). In usage, StudentID, a singular ID, a singular identifier, assigned to every novice, is mitigated to be used as the original key rather than SSN gone StudentID is possible athwart all aspects of the scheme. Reference the full whole in the quotation
CH 4: Basic SQL
#4.12 - Specify the forthcoming queries in SQL on the factsbase schema of Figure 1.2.
a) Retrieve the wheedles of all elder novices importanting in 'COSC' (computer knowledge).
b) Retrieve the wheedles of all routes taught by adherent King in 85 and 86.
c) For each minority taught by adherent King, rescue the route calculate, semester, year, and calculate of novices who took the minority.
d) Retrieve the wheedle and transcript of each elder novice (Class=5) importanting in COSC. Transcript includes route wheedle, route calculate, trustworthiness hours, semester, year, and remove for each route finishedd by the novice.
e) Retrieve the wheedles and important departments of all unswerving A novices (students who bear a remove of A in all their routes).
f) Retrieve the wheedles and important departments of all novices who do not bear any remove of A in any of their routes.
#4.15 - Consider the EMPLOYEE board’s absorption EMPSUPERFK as specified in Figure 4.2 is alterable to unravel as follows:
FOREIGN KEY (SUPERSSN) REFERNCES EMPLOYEE(SSN)
ON DELETE CASCADE ON UPDATE CASCADE,
Answer the forthcoming questions:
a. What happens when the forthcoming enjoin is run on the factsbase particularize shown in Figure 5.6?
DELETE EMPLOYEE WHERE LNAME = ‘Borg’
b. Is it improve to CASCADE or SET NULL in fact of EMPSUPERFK absorption ON DELETE?