asg3

DATABASE SYSTEM A relation R is given which is R(A,B,C,D,E). It has the following functional dependencies: BC -> D, AC -> BE, B->E . Answer the following based on this information. What is the candidate key of this relation? Is it in 2NF? Explain why or why not.

ISAM 3331: Introduction to Business Database Application Development Chapter 4 Assignment Fall 2020 Problem 1 Convert the following relation to 2NF, and then to 3NF. Show both conversions. (h, n, c, a, f, k, p, r, g, i, m, b, o, d, j, I, s, e) Functional Dependencies: m, b →I n >o p>o a >r h > g c> i h > d s>e

Problem 2: A database for maintaining information about the cities in the United States has the following relation schema Info(city_name, state, governor, mayor) The city_name attribute is the name of a city, state is the abbreviation for state the city is in, governor is the name of the governor of the state the city is in, and mayor is the name of the mayor of the city. For example, the tuple ('New York', 'NY', 'Andrew Cuomo', 'Bill de Blasio') gives information about New York City. Two cities may have the same name (for example there are at least two cities named Portland in the US), but two cities in the same state cannot have the same name. Two states cannot have the same abbreviation. Each city has exactly one mayo and each state has exactly one governor. 1. Which of the following are super keys? Which are candidate keys? (There may be more than one): {city_name} {state} {city_name, state} {city_name, state, mayor} 2. If there are 1000 cities from NY state in a relation of this…

6.3

Database Management A relation R is given which is R(A,B,C,D,E,F). It has the following functional dependencies: AB -> CDE, B -> F.  Answer the following based on this information. Is it in 3NF? If not, decompose it until 3NF.

Random Hospital Example ERD Problem Follow the Simple Procedure for ERD diagramming to design an ERD using the following user provided information.   Remember the ERD will eventually turn into the design for a database from a top down view.  Make sure you resolve any many to many conflicts.  Label an identifier or primary key and all pertinent attributes given.   If an attribute is not readily apparent from the user information, then make one up that makes sense for the particular entity.  Therefore, all entities should end up with at least an identifier and another attribute that makes sense. You may use any of the approaches shown by the book, though the ER-Model and Crow’s foot are the most common.  Make sure you show entities, relationships, cardinality, and optionality.  Random Hospital Example A hospital has 10 wards, each containing up to 20 patients.  Patients have only one doctor allowed to prescribe drugs for them.  Doctors do their rounds once a day and prescribe drugs by…

Relation algebra (Database)

Want 3NF relations for the above EER Model in the hand written format below of an unrelated example:

relational algebra

Relations describe the intersection between two axes, x and y. For example, a table (tuple) found in a database contains columns (attributes) and rows (records or observations). Database and Geospatial Mapping Application developers rely on tables and cartesian coordinate systems to adhere to the rules of relational database theory so that integrity in the location is preserved. Tables should only contain records that relate to the columns found in that table and the data domain determines what records should be stored in a given table. Database administrators can check for integrity in relationships within and between tables using structured query language (SQL). What other computer-science or IT jobs rely on relations?

39 of 40 Let R = (A, B, C, D) be a relation schema with the FD's: A → B and B → A. R has how many superkeys? (Write your answer as an integer). write your answer here

116. The decomposition of one relation say R into two relations is classified as a. functional decomposition b. ternary decomposition c. binary decomposition d. ordinary decomposition

Question 3 Consider a relation A(P, Q, R, S, T) with following functional dependencies. i. P - QR ii. R- Q iii. S-T iv. T- S Infer all possible functional dependencies, superkeys and keys.

The relation R(A,B,C,D,E) has the following functional dependencies. A, B --> C B, C --> D C, D --> E D, E --> A A) What are all of the keys of R? B) Find a lossless-join decomposition of R into BCNF relations. c) Find a lossless-join decomposition of R into 3NF relations

Also Draw Schema Diagram

Entity relationship diagram.

Relational model: In the personal model, data are prearranged in tables, which contain of table. A data in a table is called fields. Fields in a db table are also referred as qualities of a db object. A group of related fields are called as a record, which I also called as db object or entity. A group of records are called a table. Keys in a relational db table refer to a key field. To identify each record in a table, there must be at least one data field that should be unique. That is, a corresponding data field of every record should be unique. Therefore, key field is used to identify the unique record from a collection of records.

Computer Science Relational Model (Logical and Physical Schema Development) The conceptual model developed in phase 2 will be transformed into a logical and physical schema using the relational data model. The outcome of this transformation will be a relational schema. The schema should have the following components: Set of required relations All the integrity constraints Submission and Evaluation: A report consisting of the problem statement, ER diagram, relational schema And implement the database using SQL should include: All the tables properly defined /created All constraints properly defined For each table, few instances of data should be entered.

Sterile Reports Research Example ERD Problem Follow the Simple Procedure for ERD diagramming to design an ERD using the following user provided information.   Remember the ERD will eventually turn into the design for a database from a top down view.  Make sure you resolve any many to many conflicts.  Label an identifier or primary key and all pertinent attributes given.   If an attribute is not readily apparent from the user information, then make one up that makes sense for the particular entity.  Therefore, all entities should end up with at least an identifier and another attribute that makes sense. Please use a drawing tool of some type to show the ERD diagram (Word, PowerPoint, and Visio all work well).  I want you to get in the habit of drawing these with a computer instead of by hand, as this will be important as we get to more difficult examples.  You may use any of the approaches shown by the book, though the ER-Model and Crow’s foot are the most common.  Make sure you show…

q8

Here is the database design for the personnel data: employee(id, name, address) employeeMap(id, confidentialId) empSalary(confidentialId, salary)     The role 'Clerk' would be granted SELECT, UPDATE, DELETE to employee table and granted SELECT to empSalary table. While the clerk can see salary information for any statistical analysis (e.g. average salary computation), he has no idea which employee the salary is for.     The role 'Administrator' is granted rights on all three tables; using the confidentialId in the employeeMap table, she has access to salary data for individual employees too. A SQL query that the administrator can use to access all employee fields is: select e.id, e.name, e.address, es.salary from employee e inner join employeeMap m on e.id = m.id         inner join empSalary s on m.confidentialId = s.confidentialId;   Can you draw a database schema?

36. In a relation a. Ordering of rows is immaterial b. No two rows are identical c. a and b both are true d. None of these

4. Write the relation matrix for the relation R on the set (a,b.c.d.e.f) which is defined as follows: R={a,a), (a,b), (a,c), (b,a). (b,c). (c,a), (c,b), (d,d), (f.f).

Ch. 4-problem 3: Given the business rule “an employee may have many degrees,” discuss its effect on attributes, entities, and relationships. (Hint: Remember what a multivalued attribute is and how it might be implemented.) Suppose that an employee has the following degrees: BA, BS, and MBA. These degrees could be stored in a single string as a multivalued attribute named EMP_DEGREE in an EMPLOYEE table such as the one shown next: EMP_NUM EMP_LNAME EMP_DEGREE 123 Carter AA, BBA 124 O’Shanski BBA, MBA, Ph.D. 125 Jones AS 126 Ortez BS, MS   Ch. 4-problem 4: What is a composite entity, and when is it used?   Ch. 4-problem 5: Suppose you are working within the framework of the conceptual model in Figure Q4.5.   Figure Q4.5 The Conceptual Model for Question 5   Given the conceptual model in Figure Q4.5: Write the business rules that are reflected in it.   Identify all of the cardinalities.   Ch. 4-problem 6: What is a recursive relationship?…

Identify all the functional dependencies in the following relation with columns A, B, C, and D a.A→B b.A→C c.A→D d.B→A e.B→C f.B→D g.C→A h.C→B i.C→D j.D→A k.D→B l.D→C

1) Why may we need to decompose relations in a database system? Define lossy and lossless decompositions. When we decompose a relation, ideally, we would prefer to ensure two things. One is lossless decomposition. What is the other?

Suppose RR is a relation with attributes A1,A2,…,AnA1,A2,…,An. How many superkeys will RR have if a) The only candidate key is A1A1. b) The only candidate keys are A1A2A1A2 and A1A3A1A3.

MySQL Workbench 1. Normalization Concepts: Normalization is a process used in relational database design tominimize data redundancy and improve data integrity. It involves organizing the columns andtables in a database to ensure that each table contains only related data and follows specificrules. The most common normal forms are:• First Normal Form (1NF): Each table cell should contain a single value, and there should be norepeating groups.• Second Normal Form (2NF): All non-key attributes should be fully dependent on the primarykey.• Third Normal Form (3NF): All non-key attributes should be non-transitively dependent on theprimary key.2. Stored Procedures Concepts: A stored procedure is a precompiled set of SQL statements storedin a database. They can be called by applications, other stored procedures, or triggers toperform specific tasks. Some benefits of stored procedures include:• Code reusability• Improved security• Better performance• Centralized control and easier maintenance3.…

3) A relation is said to be in 3NF if it is in 2NF and no nonprime attribute of R is blank the primary key. A) an alternate forB) transitively dependent on C) Part of D) partially functionally dependent onE) fully funcionally dependent on

MySQL Workbench 1. Normalization Concepts: Normalization is a process used in relational database design tominimize data redundancy and improve data integrity. It involves organizing the columns andtables in a database to ensure that each table contains only related data and follows specificrules. The most common normal forms are:• First Normal Form (1NF): Each table cell should contain a single value, and there should be norepeating groups.• Second Normal Form (2NF): All non-key attributes should be fully dependent on the primarykey.• Third Normal Form (3NF): All non-key attributes should be non-transitively dependent on theprimary key.2. Stored Procedures Concepts: A stored procedure is a precompiled set of SQL statements storedin a database. They can be called by applications, other stored procedures, or triggers toperform specific tasks. Some benefits of stored procedures include:• Code reusability• Improved security• Better performance• Centralized control and easier maintenance3.…

Consider the Relation R4 = (A,C,B,D,E,F), with Functional Dependencies: A -> B, C -> D, E -> D. What is the Candidate Key for R4?

Database Systems: Design and Application

4. (a) Why is normalization important in database design? Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F = { AB → C, A → DE, B → F, F → GH, D → IJ }.What is the key for R? Decompose R into 2NF (Second Normal Form) and then 3NF (Third Normal Form) relations.  (b) Consider a relation schema R = (X, Y, Z) on which the following functional dependencies hold: { XY → Z, Z → X }. Show that the schema R is not in BCNF (Boyce-Codd Normal Form) but it is in 3NF (Third Normal Form).

Create a conceptual schema for the following database domain. To make it completely clear, only one (the final) conceptual schema is expected. A book is described by ISBN and a title. ISBN uniquely identifies each book. A book consists of many chapters. A chapter is described by a chapter number, title and topic. Each chapter has a unique number within a book. A chapter belongs to one book only. A book is written by one or more authors. An author can write one or more books. An author is described by the first name, last name, date of birth, and country the author comes from. A triple of values: (first name, last name and date of birth) uniquely identifies each author. We have three types of books: textbooks, fiction books and biographies. A textbook is additionally described by the titles of course it has been applied to. A textbook can be applied to none or more courses. i need answer with diagram