Assignment #3: Data DefinitionThe ER Model shown below is based on the following business rules.A manager manages one department. Each department is managed by onemanager.An employee is assigned to one project. Each project is assigned manyemployees.Employee's salary cannot be less than 0.A project's end date cannot be earlier than its start date.UID (Unique identifier) uniquely identifies an instance of the entities.DEPARTMENTPROJECTDeptNo: UIDProjectNo: UIDStartDateDNameEndDateLocationIs assignedmanaged byis hired byΕMPLOYEΕEmpNo: UIDEmpFNameEmpLNameEmpPhoneSalaryBirthDateZipcodemanagesis supervised bySupclvisesRequirements1Based on the ER Model, write SQL statements to create EMPLOYEE,DEPARTMENT and PROJECT tables.• First, you'll need to do the relational modeling. In other words, map the ERmodel to the relational model (i.e., table structure with PK and FKs)• For each column, use the data type and length that you think appropriate.• Implement all necessary data integrity rules (e.g., entity integrity constraint,key constraint, referential integrity constraint, and semantic integrityconstraints).• If necessary, make explicit assumptions.

The ER Model shown below is based on the following business rules. A manager manages one department. Each department is managed by one manager. An employee is assigned to one project. Each project is assigned many employees. Employee's salary cannot be less than 0. A project's end date cannot be earlier than its start date. UID (Unique identifier) uniquely identifies an instance of the entities. DEPARTMENT PROJECT DeptNo: UID DName ProjectNo: UID StartDate EndDate Location Is assigned is managed by is hired by ΕMPLOYEE EmpNo: UID EmpFName EmpLName EmpPhone Salary BirthDate manages iş supervised by Zipcode Supclvises Requirements 1 Based on the ER Model, write SQL statements to create EMPLOYEE, DEPARTMENT and PROJECT tables. • First, you'll need to do the relational modeling. In other words, map the ER model to the relational model (i.e., table structure with PK and FKs) • For each column, use the data type and length that you think appropriate. • Implement all necessary data integrity rules (e.g., entity integrity constraint, key constraint, referential integrity constraint, and semantic integrity constraints). • If necessary, make explicit assumptions.

The ER Model shown below is based on the following business rules. A manager manages one department. Each department is managed by one manager. An employee is assigned to one project. Each project is assigned many employees. Employee's salary cannot be less than 0. A project's end date cannot be earlier than its start date. UID (Unique identifier) uniquely identifies an instance of the entities. DEPARTMENT PROJECT DeptNo: UID DName ProjectNo: UID StartDate EndDate Location Is assigned is managed by is hired by ΕMPLOYEE EmpNo: UID EmpFName EmpLName EmpPhone Salary BirthDate manages iş supervised by Zipcode Supclvises Requirements 1 Based on the ER Model, write SQL statements to create EMPLOYEE, DEPARTMENT and PROJECT tables. • First, you'll need to do the relational modeling. In other words, map the ER model to the relational model (i.e., table structure with PK and FKs) • For each column, use the data type and length that you think appropriate. • Implement all necessary data integrity rules (e.g., entity integrity constraint, key constraint, referential integrity constraint, and semantic integrity constraints). • If necessary, make explicit assumptions.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Suppose the due date for a book depends not only on the date the book issued but also on the following factors; - member type (assume 3 types of member -regular, senior, academics- and define rule for these types) - number of books already issued to the member (define a rule yourself) For the requirement defined above, answer the following questions;- Update all Use Case(s) which are affected- Update the Domain Model if necessary- Update all Sequence Diagram(s) and discuss your design in terms of Grasp Patterns Define a simple tax (%10 percent of the sale is added to the sale total) for sale processing. - Update all Use Case(s) which are affected - Update the Domain Model if necessary - Update all Sequence Diagram(s) and discuss your design in terms of Grasp Patterns
Q1: All the following statements about weak and strong entity sets are True, Except: Select one: a. The weak entity set depends on the existence of the strong entity set b. PK of the strong entity set is not explicitly stored with the weak entity set c. An entity set that does not have a primary key is referred to as a weak entity set d. In ER-Diagram, the identifying relationship of a weak entity in represented as a double diamond e. The PK of the weak entity set is only formed by the primary key of the strong entity set Below are two pictures, each with a second and third question
DEL (30 %): Normalization:Let R bea relational schema and FR corresponding set of functional dependencies.R = ABCDEFR = {A → C, B → D, C → A}a) What normal form is the R schema in?b) Gradually normalize the scheme to 3NF, and for each new scheme, specify thekeys and associated functional dependencies
Clayton Elementary School is converting the wish list request form below into online form because of COVID-19. 1. Class Diagram: Class name, attributes, and data type Operations (Methods) that you need to perform 'Fill Wishlist' Relationship between classes. Multiplicity of each relationship Assumptions that you need if any Sequence Diagram: Actor, boundary, and control objects Objects that participate this use case Messages, operations, etc. that indicate a successful filling of the form as illustrated in the form above.
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…
Given the following relation that stores details of employees and their projects: If the functional dependencies are consistent with the above relation, put TRUE. If they are not, put FALSE. 1.ProjectID ➡ EmployeeName 2.ProjectID ➡ EmployeeSalary 3.ProjectID , EmployeeName ➡ EmployeeSalary 4.EmployeeName ➡ EmployeeSalary 5.EmployeeSalary ➡ ProjectID 6.EmployeeSalary ➡ EmployeeName , ProjectID
25. Which expression below will return the time an individual entity spent in the system in Simio, using built-in Simio functions (i.e. not user-defined functions)? a. TimeNow - TimeCreated b. TimeInSystem c. ModelEntity.TimeInSystem d. TimeNow - ModelEntity.TimeCreated e. TimeNow - ModelEntity.TimeInSystem
If any of the following choices are applicable, please tick them. This model includes entities like as customers and accounts; employees; checks; and transactions. Using recursive relationships correctly, which of the following is the proper choice?(A) Checks drawn on a checking or savings account are referred to as "checks."Bank transactions that are both credit and debit belong to the same account, as do checks, and so do employees and their managers.
PART 1The goal of this assignment is to design a conceptual schema using the (E)ER Data model.(E)ER data modelDesign a schema that incorporates the specification described below as efficiently as possible.You should submit a written diagram of your schema design using the notation given in theclass. In this diagram, indicate all the classes, subclasses, relationships (weak & strong),relationship cardinalities and degrees, total participations, attributes, and primary keys. Inaddition, specify whether each attribute is single-valued or multi-valued, stored or derived, andatomic or composite. In your design, you can make and state reasonable assumptions if they arenot specified in the specification.Design SpecificationYelp is a local business directory service and review site with social networking features. Itallows users to give ratings and review businesses. You are going to design the database systemfor Yelp (www.yelp.com). It should store and manage the following information but…
The following table is a sample weekly report about animals in a certain household. Provide a conceptual schema for this UoD including uniqueness constraints, mandatory roles, value constraints and subtype constraints. Include a definition for each subtype. Animal Animal Kind sex Nr cars chased Nr mice caught Fido dog M 12 - Felix cat M - 1 Fluffy dog F 0 - Tweetie bird F - -
1. Consider the following relations:Student (snum: integer, sname: string, major: string, level: string, age: integer)Class (name: string, meets at: string, room: string, fid: integer)Enrolled (snum: integer, cname: string)Faculty (fid: integer, fname: string, deptid: integer)The meaning of these relations is straightforward; for example, Enrolled has onerecord per student-class pair such that the student is enrolled in the class. Level is atwo character code with 4 different values (example: Junior: A Level etc)Write the following queries in SQL. No duplicates should be printed in any of theanswers.i. Find the names of all Juniors (level = A Level) who are enrolled in a classtaught by Prof. Kwizeraii. ii. Find the names of all classes that either meet in room R128 or have five ormore Students enrolled.iii. Find the names of all students who are enrolled in two classes that meet atthe same time.
1. What technique is used to model the situation that Student may have multiple addresses? A) Associative Entity (gerund)B) Weak entitiesC) Super/sub typesD) Recursive relationships 2.There is m:m relationship between students and classes. Which technique is used forrepresenting the student grade? A) Associative Entity (gerund)B) Weak entitiesC) Super/sub typesD) Recursive relationships 3. An associative Entity (gerund) may be used whenever there are data that depends on twoentities even when their relationship is 1:m. True or FalseA) TrueB) False 4. (check all that apply) When do you use recursive relationships?A) the relationship between employees and their supervisorsB) the relationship between employees and their dependentsC) the relationship between employees and their departmentsD) the relationship between employees and their team membersE) the relationship between employees and their job assignments 5. How is a gerund converted into a relational model?A) ignore the gerund in the…