What is the difference between controlled and uncontrolled redundancy

Controlled redundancy is the state to improve the performance of queries. We want to control redundancy and retrieve the data along with the all the details. By placing all the data together, we do not have to search multiple files to collect this data. In some cases, the DBMS should control this redundancy in order to prohibit inconsistencies among the files. A controlled redundancy is a necessary technique to use redundant fields in a database. When a record showed that is inconsistent with the data file and when the DBMS has no control over this, we have uncontrolled redundancy. An example, of controlled and uncontrolled redundancy, is if you put in a document please enter your name and the input could be numbers, symbols, letter and multiple letters with a maximum of x things. But we could have controlled redundancy and put a rule where we only allow 12 letter no symbol or numbers allowed.

1.1 Specify all the relationships among the records of the database shown in Figure 1.2. The relationship among the records of the database are as follows There is an example, of a database in a university trying to maintaining information concerning students, courses, section, grades report and a prerequisite in the university environment. The database that is shown is organized as follow STUDENT In the STUDENT they record stored data of the name, student_number, class and major. COURSE In the COURSE they record stored the stored data on each course. That includes the data Course_name, Course_number, Credit_hours, and Department. SECTION In the SECTION they record stored data of the course. Section_Identifier, Course_Number, Credits_hours and Department. And it is related to the COURSE data records. GRADE_REPORT In the GRADE_REPORT they record stored grades of the students and receive inputs from sections they have completed. And it is related to the STUDENT and SECTION in data records. PREREQUISITE PREREQUISITE they record stored prerequisites for each course. And it is related to the COURSE with the data records. 1.12 Cite some examples of integrity constraints that you think can apply to the database shown in Figure 1.2. According to Navathe integrity constraints must hold for the data.

The simplest type of integrity constraint involves specifying a data type for each data item (Elmasri Navathe, 2016). For example, StudentNumber should be unique for each STUDENT record. CourseNumber should be unique for each COURSE record and the value in CourseNumber in a SECTION record must also exist in some COURSE record. The value of StudentNumber in a GRADE_REPORT record must also exist in some STUDENT record (Elmasri Navathe, 2016).. 1.13 – Give examples of systems in which it may make sense to use traditional file processing instead of a database approach. The best examples of a system that uses file processing should a system that are simple, well-defined database applications that are not expected to change at all. Stringent, real-time requirements for some application programs that may not be met because of DBMS overhead. Embedded systems with limited storage capacity, where a general-purpose. Database approach would not fit. No multiple-user access to data (Elmasri Navathe, 2016). So in others, words will make more sense the use of minor structure to locate files or a single user system that does not require security. 1.14 – Consider Figure 1.2. 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.

The Columns in the database that would need to get updated are in Student the Major, of Course, the CourseNumber, CourseDepartment, SectionCourseNumber, PrerequisiteCourseNumber, and PrerequisitePrerequisiteNumber. Can you restructure the columns in COURSE, SECTION, and PREREQUISITE tables so that only one column will need to be updated Table Column(s)Column(s)STUDENT MajorCOURSE CourseNumber and DepartmentSECTIONCourseNumberPREREQUISITE CourseNumber and PrerequisiteNumber TableColumnSplit Columns COURSE CourseNumber CourseDept and CourseNumSECTION CourseNumber CourseDept and CourseNumPREREQUISITE CourseNumber CourseDept and CourseNumPREREQUISITE PrerequisiteNumber PrerequisiteDept PrerequisiteNum All the column of the course number will be split into two columns and one of them for the course department and one for the course number. The table in the in the COURSE table will be CourseNumber and CourseDept and CourseNum. In SECTION will be as follow, CourseNumber will be CourseDept and CourseNum in PREREQUISITE table, CourseNumber CourseDept, and CourseNum and in Prerequisite will be PrerequisiteNumber PrerequisiteDept and PrerequisiteNum 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 According to the book on page 50 Figure 2.7 Logical three-tier client/server architecture could be the best designing Web based system to make airline reservations and to sell airline tickets. On page 50, explain how the user interface, application rules, and data access act as the three tiers and client consists of web user interface and the web server have the application logic that includes all the rules and regulations related to the reservation process and the issue of tickets and the database server contains the DBMS.

On Page 46 in section 2.5.1 The centralized DBMS architecture would not work since the user interface and database server are on different machines for a web-based system. Page 47 Centralized DBMS in which all the DBMS functionality application program execution and user interface processing were carried out on one machine. The basic client/server architecture and the two-tier client/server architecture would work the best for this logic can reside on a server other than the DBMS Server. 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 TableColumn(s)COURSECourseNumber Holds the group of department and number that must be unique within the department. We could make this a combination of CourseNumber and CourseName, PREREQUISITEThe combination of CourseNumber and PrerequisiteNumberSECTIONSectionIdentifier Lets assume there are no two sections can have the same SectionIdentifier.

If we were to consider that SectionIdentifier is unique then the answer changes to the group of SectionIdentifier, CourseNumber, Semester, and Year.GRADE REPORTStudentNumber and SectionIdentifier specified in SECTION, the SectionIdentifier will be different if a student takes the same course or different courses in other terms CH 3 THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE CONSTRAINTS 3.17 – Composite and multi-valued attributes can be nested to any number of levels. Suppose we want to design an attribute for a STUDENT entity type to keep track of previous college education. Such an attribute will have one entry for each college previously attended, and this entry is composed of college name, start and end dates, degree entries (degrees awarded at that college, if any), and transcript entries (courses completed at that college, if any). Each degree entry is formed of degree name and the month and year it was awarded, and each transcript entry is formed of a course name, semester, year, and grade. Design an attribute to hold this information. Use the conventions of Figure 7.5. previous education ( CollegeName, StartDate, EndDate, Degree (DegreeName, Month, Year) , Transcript (CourseName, Semester, Year, Grade) ) 3.22 – A database is being constructed to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game for each team, the positions they played in that game, and the result of the game.

Try to design an ER schema diagram for this application, stating any assumptions you make. Choose your favorite sport (soccer, football, baseball …). Let assume a baseball league design please see below SHAPE MERGEFORMAT CHAPTER 4 THE ENHANCED ENTITY-RELATIONSHIP (EER) MODEL 4.17 – Consider the BANKER schema of Figure 3.21, and suppose that it is necessary to keep track of different types of ACCOUNTS (SAVINGS_ACCTS, CHECKING_ACCTS,…) and LOANS (CAR_LOANS, HOME_LOANS, …). Suppose that it is also desirable to keep track of each accounts TRANSACTIONs (deposits, withdrawals, checks, …) and each loans PAYMENTs both of these include the amount, date, time, … Modify the BANK schema, using ER and EER concepts of specialization and generalization. State any assumptions you make about the additional requirements. SHAPE MERGEFORMAT SHAPE MERGEFORMAT Reference Elmasri, R., Navathe, S. B. (2016). Fundamentals of Database Systems 7th Edition, New York, Pearson Addison-Wesley, Fundamentals of Database Systems Seventh Edition (pp. 3-56). INFO620 Enterprise Database Systems Assignments Page PAGE MERGEFORMAT I American Public University System Irizarry PNAME PLAYER PLAYS FOR PARTICIPATES PERFORMANCE START END DATE GAME GAME KEY DATE TIME FIELD HOMETEAM INNING SCORE INNING RUNS HITS ERRORS FINAL SCORE RUNS HITS ERRORS TEAM TNAME HOMETEAM INNING FINAL SCORE INNING RUNS HITS ERRORS RUNS HITS ERRORS AWAY TEAM FINALSCORING INNINGSCORE INNING RUNS HITA ERRORS RUNS HITS ERROR Account Saving Checking Trust D AT TRANSACTION DATE/TIME DATE TIME AMOUNT TYPE LOAN D LP PAYMENT CAR HOME CREDIT LINE PERSONAL DATE TYPE AMOUNT Y, B8L 1(IzZYrH9pd4n(KgVB,lDAeX)Ly5otebW3gpj/gQjZTae9i5j5fE514g7vnO( ,[email protected] /e5sZWfPtfkA0zUw@tAm4T2j 6Q OQHVa z2