Logics of Database Analysis - Assignment Example

Table of Contents Assignment 1 2 Question 1 2 Question 2 4 Question 3 5 Question 4 8 Assignment 2 10 Question 1 10 Question 2 12 Question 3 13 Question 4 13 Question 5 14 Assignment 1 Question 1 a). There many differences between a database schema and a database state among them include the definition. The differences is as follows Database Schema is the features that do change frequently that are describing data using database structure and the constraints while Database state is the data that is stored and is subject to alteration thus can change any time. Any update to the database will change the database state. b) The differences are between logical data-independence and physical data independence is shown as Logical data-independence is the ability to change concept schema without altering other schemas and related applications. This alters things like descriptions of tables, classes and XML tags. Physical Data Independence is where one is able to change internal schema without touches the conceptual schema. He is able to change the data storing mechanisms used in the real world like the number of partitions required, table spaces and the number of CPU’s required. c) The differences between tables in SQL and relational model SQL tables allows for duplicate tuples and anomalies while the relational model does not allow duplicate tuples and other anomalies. The relational model is storage management system could be relatively simple, and provide access to large data quantities. The identification of elements of data in relation utilizes column name, a primary key, and a timestamp. d) The operations of relational algebra are There are a number of operations in relational algebra which are used in carrying various operations in database management. These include union, select, rename, project, natural join, set difference, Cartesian product, division, assignment and set intersection. These can be described as; Union- the union operation is applicable when database data needs to show that they are in more than one relation thus some of its attributes has to appear in both relations. Select operation is used to identify tuples of a relation as well as selects attributes or tuples of a ascertain condition such as choosing publishers who have published books within the subject DataBases. Rename operation is used in renaming of tuples Project operation is used in returning an argument relation of some tuples or relations. Natural join operation is combines two operations that is select and Cartesian product to work on some relation. Set difference operation enables one to find relations in a specific tuples. Cartesian product operation one used get information from two relations Division operation allows one to carry out quotient operation on tuples. Set intersection operation allows one find tuples in more than one relation. Question 2 a). Relationship Diagram to created will have two main entities client_Details entity and message details entity. The message entity will have message id (mId), a subject (subject), sendDate and a body (body) while A client entity has an email address (email), and client’s name, gender, householdSize, and address. The Entity – Relationship Diagram is shown below; b) Translating the E-R diagram into SQL tables CREATE TABLE Client ( EmailAdress LONG, ClientName TEXT(40), Gender TEXT (10) ClienthouseholdSize LONG, clientaddress ) CREATE TABLE Message ( MessageID LONG, PRIMARY KEY, MessageSubject TEXT(120) Message Body TEXT(100) SendDate LONG ) c) specifying that the subject of a message must not be longer than 120 characters, in SQL is by using the term text(120) as shown in the diagram below CREATE TABLE Colors ( MessageID LONG, PRIMARY KEY, MessageSubject TEXT(120) Message Body TEXT(100) SendDate LONG ) d) Enforcing the restriction that the only valid values of sex are male or female, in SQL is as shown CREATE TABLE Client ( EmailAdress LONG, ClientName TEXT(40), Gender TEXT (10), CONSTRAINT PK_Male and female PRIMARY KEY ClienthouseholdSize LONG, clientaddress ) Question 3 Entity – Relationship Diagram (E R Diagram) The database to create will contain various entities which include member details, client details contacts and contracts. The following information has been used to develop Entity – Relationship Diagram. The first entity will be Contact _details which will have member ID, email address, their name (first, last and middle), a password, a postal address, and a phone number. The contacts are her engineer who works for the company or clients that the company is servicing. The other entity is project entity containing a unique project number, a name, a start date and a target end date and project will be either products or services or both. Products that are produced as part of a project will be manufactured by a manufacturer or completed by service provider. The manufacturers and service providers have unique identification number and a name in addition the manufacturers has a monthly production capacity. Question 4 a). List all students that studied the course with CrsName 'Database Systems' display *total number in_ CrsName 'Database Systems' _ where CATEGORY= ’CS305’ IT WILL DISPLAY b) the names of all students who took courses both F1995 AND S1996 Semesters display *students took ID and Name who took F1995 AND s1996 _ where CATEGORY= ’F1995 and IT WILL DISPLAY c) the names of all professors that taught a course in which at least one student achieved a grade of 'A' display *professor name ID whose transcript grade A _ where CATEGORY= GRADE A’ IT WILL DISPLAY d) List CrsNames for all courses that have been taken by all students. display * CrsNames name student name transcript grade A _ where CATEGORY= CrsNames’ Assignment 2 Question 1 A). Discuss insertion, deletion and modification anomalies and illustrate with simple examples why they are bad. In the database anomalies occurs when some information is missing or unmatched due to keying in errors. When database anomalies are modified, inserted or deleted then inconsistencies may occur leading to data redundancy. This may affect the usability of the database information. When insertion takes place proper corrections needs to be done to the entities of the data. For example if the department changes its name. the details of employees working in the depart must changed to indicate the name the department as changed. It is through this process that the anomalies shall be given proper attention to hence making a definite consideration on how database flows. b) Describe what the NULL value and Dangling Tuple problems are and how they occur in a database. Null values will create a problem to the data if they are have joint attributes thus requiring further action. Dangling Tuple have a problem since they have primary key that needs to be repeated due to partitioning of data into various attributes. There should be validation process has been taken care of through restriction on the type of data entered . To prevent the field from showing null entries specify the command ‘NOT NULL’ at the end of a query for adding fields to a table. A screen shot of the validation procedure is shown below c) normal forms alone not sufficient as a condition to ensure good database design Because A normal form is a set of guidelines to normalize a database and the normal forms range from the first normal form (1NF) to the fifth normal form (5NF). However they fail to show some relationship which can be shown by E-R diagram d) We can have one primary or clustering index on a file because it will be easy to retrieve. Each file has a primary key to identify it in the database . it is possible to have multiple key when file contain multiple sectorial data. This means a foreign key to cross-reference one file with another. Question 2 The relational database schema for the data provided. The 3NF-normalized tables are represented in the following diagram. Question 3 From the database schema having attributes A, B, C, D, and E and functional dependencies: The algorithm is done in the following steps Question 4 The functional dependencies over the attribute set A,B,C,D,E,F shown below : The minimal cover to decompose into lossless 3NF by applying the algorithm is It can be noted that AC and ACF are in the category of ABNF and ACD is not the same category A->ABCD, B->ABCD, C->ABCD, D->ABCD Thus, candidate keys are A,B,C,Df Since all are candidate keys, there is NO BCNF Nor 3NF violations. No decomposition necessary. Question 5 Read More