asg3

Relational Database A relation R is given which is R(A,B,C,D,E). It has the following functional dependencies: BC -> E,  E -> A and B -> D. Answer the following based on this information. Find all the candidate keys. What type of dependencies are found in this relation? Which normal form does this relation satisfy? Is it in 3NF? If not, decompose it until 3NF

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

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:

Defined the relational algebra operations as follows: SELECT (p, R) is equivalent to σp(R) referring to a set tuples of relation R that conforms to predicate p.  PROJECT(L, R)  is equivalent to  πL(R) referring to a relation that contains a vertical subset of R, extracting the values of specified attributes L and eliminating duplicates. JOIN (R, p, S) refers to a relation containing a combination of tuples of relations R and S that satisfy the join condition p. FULL_JOIN (R, p, S) refers to a relation containing the result of outer join between  relations R and S that satisfy the join condition p LEFT_JOIN (R, p, S) refers to a relation containing the result of left outer join between  relations R and S that satisfy the join condition p RIGHT_JOIN (R, p, S) refers to a relation containing the result of right outer join between  relations R and S that satisfy the join condition p CROSS (R, S) refers to a relation containing the catesian product of relations  R and S.  UNION (R, S)…

Defined the relational algebra operations as follows: SELECT (p, R) is equivalent to σp(R) referring to a set tuples of relation R that conforms to predicate p.  PROJECT(L, R)  is equivalent to  πL(R) referring to a relation that contains a vertical subset of R, extracting the values of specified attributes L and eliminating duplicates. JOIN (R, p, S) refers to a relation containing a combination of tuples of relations R and S that satisfy the join condition p. FULL_JOIN (R, p, S) refers to a relation containing the result of outer join between  relations R and S that satisfy the join condition p LEFT_JOIN (R, p, S) refers to a relation containing the result of left outer join between  relations R and S that satisfy the join condition p RIGHT_JOIN (R, p, S) refers to a relation containing the result of right outer join between  relations R and S that satisfy the join condition p CROSS (R, S) refers to a relation containing the catesian product of relations  R and S.  UNION (R, S)…

relational algebra

Let us consider the following relational database. Students(stuID, lastName, firstName, major, gpa) Faculty(facID, name, deptName, rank) Classes(classNhmber, facID, schedule, room) Enrolls(stuID, classNumber, grade) The primary keys are underlined. The referential integrity constraints are as follows: The column facID of relation Classes that references table Faculty, The column of stuID of relation Enrolls that references table Students, and The column classNumber of relation Enrolls that references table Classes. The following SQL statement is intended to define the table Students. However, this statement does not work correctly. CREAT TABLE Students( lastName VARCHAR(20), firstName VARCHAR(20), major VARCHAR(40) gpa DECIMAL(3,2), PRIMARY KEY(stuID)); However, this statement does not work correctly. Briefly explain why and list the corrected SQL statement.

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

CSC 472 Introduction to Database Systems Project Consider the contents of output tuples generated by a join operator. The advantage of "late materialization" approach is that future operators in the query plan never need to go back to the base tables to get more data. Group of answer choices True False

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.

Here's a relation (R), its attributes and its functional dependencies (F): R(A, B, C, D, E, F, G) BC -> DG CD -> F FG -> A   1. compute the closure of {ACD}+ 2. compute the closure of {F}+ 3. what are the keys of R?

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.

Problem 1: A database for an online store has the following relational schema to keep track of customers' orders: ItemOrder(cust_id, item_id, color, size, price) Here primary key is underlined and bolded and assume that the following functional dependency holds on the above relational schema: item_ID, size →price Fill in the blanks using attributes from ItemOrder schema: The above functional dependency says that if two rows have the same values for then they have the same values for

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…

Read Scnerio carefully :  Make a Propose and optimal database design in RDBMS for a super store. In which clients came and can purchase products according to their likes and dislikes. Super store have same products of different companies. In the end client received a receipt of their purchase from counter he/she go for bill. Database also save records of stock according to FIFO system. Required to define Entities and their attributes Attributes Types  Each Attribute Required  Define keys of each entity Define relationship between the entities.

branch(branchhame, branch+citysassets} customer (HD, Customer name, customer street, customer city) loan loan number branch_name, amounty borrower(ID; loan_number account (account_number, branch name, balance)e depositor (HDs account number)- Figure le Consider the bank database in Figure 1, where the primary keys are underlined. Each branch might have many loans or accounts, associated with borrowers or depositors, respectively. Construct the following SQL queries for this relational database. 1. Find the name of each branch that has at least one customer who has an account in the bank and who lives in the "Harrison" city. Make sure each branch name only appears once. 2. Find the ID of each customer who lives on the same street and in the same city as customer "12345". (1) Please use "tuple variables". e (2) Please use "derived relations" or “with". 3. Find the ID of each customer of the bank who has an account but not a loan. 4. Find the total sum of all loan amounts for each branch…

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

Strictly avoid existing answer

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).

Given the following relation R and its functional dependencies: R(workerNumber, repairNumber, workerName, machineNumber, spentTime, repairDate, machineName, workshopNumber, workshopName )   workerNumber à workerName repairNumber à repairDate, machineNumber machineNumber à machineName, workshopNumber workshopNumber à workshopName 1)Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table

: Consider a database that consists of the following relations. SUPPLIER(Sno, Sname)) PART(Pno, Pname) PROJECT(Jno, Jname) SUPPLY (Sne, Pne, Jno) The database records information about suppliers, parts, and projects and includes a ternary relationship between suppliers, parts, and projects. This relationship is a many many-many relationship. Specify and execute the following queries. (Answer five only) a. Retrieve the part numbers that are supplied to exactly two projects. b. Retrieve the names of suppliers who supply more than two parts to project 'J1'. c. Retrieve the part numbers that are supplied by every supplier. d. Retrieve the project names that are supplied by supplier 'S1' only. e. Retrieve the names of suppliers who supply at least two different parts each to at least two different projects f. Retrieve the part numbers that are supplied by every project