Use the database shown in Figure P3.10 to work. Note that the database is composed of four tables that reflect these relationships: • An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEEs. • An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many EMPLOYEEs. Note also that the M:N relationship has been broken down into two 1:M relationships for which the BENEFIT table serves as the composite or bridge entity For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None.

Use the database shown in Figure P3.10 to work. Note that the database is composed of four tables that reflect these relationships: • An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEEs. • An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many EMPLOYEEs. Note also that the M:N relationship has been broken down into two 1:M relationships for which the BENEFIT table serves as the composite or bridge entity For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None.

Fundamentals of Information Systems

9th Edition

ISBN:9781337097536

Author:Ralph Stair, George Reynolds

Publisher:Ralph Stair, George Reynolds

Chapter3: Database Systems And Big Data

Section: Chapter Questions

Problem 4SAT

See similar textbooks

Similar questions

Use the database shown in Figure P3.10 to work Problems 10-16. Note that the database is composed of four tables that reflect these relationships: An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEES. An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many EMPLOYEES. Note also that the M: N relationship has been broken down into two 1: M relationships for which the BENEFIT table serves as the composite or bridge entity. For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None. Create the ERD to show the relationship between EMPLOYEE and JOB
Consider the relation r(A, B, C), where the characteristic A serves as the index to the relationship. Give an example of a question that can only be answered by looking at the index, rather than the tuples in the relationship, and why this is so. (Index-only plans are query plans that solely make use of the index and do not touch the underlying relational database.)
Database Systems Question 6. Consider the following relation: Invoice(Order_ID, Order_Date, Customer_ID, Customer_Name,Customer_Address,Product_ID, Product_Desc, Price, Order_Quantity) FD1 - Order_ID -> Order_Date, Customer_ID, Customer_Name, Customer_Address FD2 - Customer_ID -> Customer_Name, Customer_Address FD3 - Product_ID -> Product_Description, Price FD4 - Order_ID, Product_ID -> Order_Quantity What are the candidate keys of this relation? Give reasons to explain if this is in 1NF or not. If not, decompose it to 1NF. Give reasons to explain if this is in 2NF or not. If not, decompose it to 2NF. Give reasons to explain if this is in 3NF or not. If not, decompose it to 3NF.
Map (or transform) EER Model diagram below into a relational database schema. Take into account all the constraints in your EER model. Underline the primary key attributes of each relation and show each foreign key as a directed arrow from the referencing attribute(s) to the referenced attribute(s) in the referenced relation.
This test concerns database design, including both the logical design via the Entity Relationship (E - R) model and the physical model via relational database tables. The subject of the model is the registration of students in college classes, and there may be multiple classes for each course. The college model should include: courses (for example the CBU catalog), the current classes (or course-sections) given for courses (for example the CBU schedule for a term), the faculty qualified to teach courses, the scheduling of classes (to buildings/rooms, times, and faculty members), the assignment of faculty to students for advising (1 advisor per student), and the scheduling of students to classes.For unique identifiers you can use the following abbreviations:SID Student IDCID Course ID (ie MIS 471, …)SEC Course Section (ie A, B, C, …)BID Building ID (ie BU, BH, K, …)RID Room NumberFID Faculty IDTID Timeslot ID (ie TR 9:30, MWF 8am, …)CRN Course Record Number (an auto key for each…
Consider the relation r(A, B, C), where the index is the attribute A. Give an example of a query that can only be answered by looking at the index rather than the relationship's tuples. (Index-only plans are query plans that rely just on the index rather than the actual relation.)
Read the following business rules carefully and use common sense when necessary to come up with a good database design as follows: 1- First, develop an ER model. Make sure to use meaningful entity names and relations. 2- Translate ER model into a set of tables, relations and constraints. 3- Label appropriate columns as primary keys and foreign keys. Business rules: A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one patient. Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as “unscheduled.” If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment. With each visit, the patient’s records are updated to provide a medical history Each patient visit…
Note: This question has been rejected three times now saying the question is incomplete. That's how the question is. Please do not reject it saying it is incomplete. The previous 4 questions were related. I was asked to provide question 3 but I will provide all of them. Question 1: Create the Entity Relationship (ER) Diagram for the following scenario. We want to maintain a database for a Faculty within a multi-campus regional university. The faculty has a number of schools and each school is characterized by its school-code which is unique, name and campus location. Schools employ professors who are characterized by their unique staff-id, name and the school they work for. A school offers courses that are characterized by their course-code which is unique, course name and the school. There are subjects offered within the faculty and these arecharacterized by unique subject-code, title and the school that offers them. Students enroll into courses that are characterized by student-id…
Please draw an relational ERD with multiplicities and all the attributes based on following description: ( Please identify the primary key in each entity.) An Adult Education Department runs various courses during the daytime and evenings, and at different times of the year. For example, ‘Spanish level 1’ is offered on Monday mornings, Monday evenings or Wednesday evenings, and runs over 25 weeks from October to March. On the other hand, ‘Introduction to Digging Up Your Ancestors’ only runs for 8 weeks, but is offered on Tuesday or Wednesday evenings from October to December, January to March, and April to June, with an optional field week in August. There is always a maximum number of places for each course offering, which is dependent on the individual teacher. For example, ‘Spanish level 1’ on Monday evenings may be limited to 20 places, but on Wednesday evenings the limit may be 25. Each course offering is only taken by one teacher, however teacher may take…
Consider the ER diagram shown in Figure 3.22 for part of a BANK database. Each bank can have multiple branches, and each branch can have multiple accounts and loans. a. List the names of all relationship types, and specify the (min, max) constraint on each participation of an entity type in a relationship type. Justify your choices. b. Suppose that every customer must have at least one account but is restricted to at most two loans at a time, and that a bank branch cannot have more than 1,000 loans. How does this show up on the (min, max) constraints?
Data/Database Management system . Consider the ER diagram shown below. Supply the relationship constraints using cardinality ratios and participation constraints on this diagram based on the following assumptions (you may use (min, max) notation if you prefer):  An employee may work in up to two departments or may not be assigned to any department.  Each department must have one and may have up to three phone numbers.  Each department can have anywhere between 1 and 10 employees.  Each phone is used by one, and only one, department.  Each phone is assigned to at least one, and may be assigned to up to 10 employees.  Each employee is assigned at least one, but no more than 6 phones.
SUBJECT: DATABASE MANAGEMENT SYSTEM QUESTION NO 1: Suppose you assigned a task to design relational schema by consider the domain of Bahria University and consider the information of student, employee, campuses, departments, and programs and then normalize these relations up to third normal form QUESTION NO 2: Suppose you assigned a task to design relational schema by considering the domain of product manufacturing company by considering the information of clients, branches, employee, products, and human resource then identify sample dependencies(if any) associated with BCNF and 4th Normal form and then remove those dependencies in order to transform the schemas into BCNF and 4th Normal Form also.