4. Using the following sample relation and your knowledge of declarative constraints: STUDENT (Title, Name, Gender, Birthday, Address, Email, Mobile, SID, MID, PROJID, PLACID) • SID is the student identifier • MID is the identifier of the compulsory assigned staff mentor (Table: STAFF) • PROJID is the identifier of the optional project selected by the student (Table: PROJECT) • PLACID is the identifier of the optional industrial placement undertaken by the student (Table:PLACEMENT) Address each of the following tasks. a. Suggest and justify a PRIMARY KEY for this relation. Write the correct partial SQL code to implement this. (Note : Write only SQL command to create primary key)

4. Using the following sample relation and your knowledge of declarative constraints: STUDENT (Title, Name, Gender, Birthday, Address, Email, Mobile, SID, MID, PROJID, PLACID) • SID is the student identifier • MID is the identifier of the compulsory assigned staff mentor (Table: STAFF) • PROJID is the identifier of the optional project selected by the student (Table: PROJECT) • PLACID is the identifier of the optional industrial placement undertaken by the student (Table:PLACEMENT) Address each of the following tasks. a. Suggest and justify a PRIMARY KEY for this relation. Write the correct partial SQL code to implement this. (Note : Write only SQL command to create primary key)

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

In the attached diagram, which of the following statements are true? primary key for section is course_id, sec_id, semester, year primary key for section is equivalent to the primary key for course unioned with the discrimator section is a weak entity set whose existence depends on course the discrimator of section is sec_id, semester, year
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
Convert the ER diagram to relations in the normal manner to capture as many constraints as possible, and then identify which of the answers is a relation schema. A. Dependents(ID, name, bDate) B. Employees(ID, name, street, city, state, phones, sDate, lengthEmp) C. Coverage(ID, name) D. Employees(ID, name, street, city, state, sDate, lengthEmp)
Lab 3: Normalization Assignment: Problem #11 from Chapter #6. (Be sure to note that this is PROBLEM and not REVIEW QUESTION). Normalize a set of data to 1st, 2nd and 3rd normal form by creating relational schemas, dependency diagrams and an Entity Relationship Diagram (crow’s foot notation). For this lab, complete problem #11 in Chapter 6. Be sure to complete each letter (a thru c) for the problem. The ERD for letter cmust be completed with the use of Microsoft Visio. Remember to include headers on all documents you submit to include a brief descriptive title, your name and a date. Question 11: Given the sample records in the CHARTER table shown in Table P6.11, do the following: Write the relational schema and draw the dependency diagram for the table structure. Make sure that you label all dependencies. CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry is based on round-trip miles, including pickup points. (Hint: Look at the data values to determine the…
Q2 Review the data model provided in the case study. (1) List all entities and its type and (2) identify the type of participation constraint for each existing relationship and why the type you chose is correct.
convert the design (ER diagram) into relational schemas and capture as many of the constraints as possible.
Use the following business rules to create a Crow’s Foot Entity Relationship Diagram. Write all appropriate connectivities and cardinalities in the ERD. Add other linking entities as needed. • A department employs many employees, but each employee is employed by one department. • Some employees, known as “rovers,” are not assigned to any department. • A division operates many departments, but each department is operated by one division. • An employee may be assigned many projects, and a project may have many employees assigned to it. • A project must have at least one employee assigned to it. • One of the employees manages each department, and each department is managed by only one employee. • One of the employees runs each division, and each division is run by only one employee.
Make an Entity relationship diagram and use the Erdplus and apply these requirments Using the Erdplus(use it to make this diagram, the answer should be as a Diagram) Q) Create an entity-relationship diagram (ERD) using min-max notation based on the following datarequirements of the University database:1. The university is organized into colleges, and each college has a unique number, unique name, and a particularfaculty member who is the dean of the college. Each college administers a number of academic departments.2. Each department has a unique name, a unique code number, and a particular faculty member who chairs thedepartment. We need to keep track of the start date when that faculty member began chairing the department.In addition, each department may have several faculties and must be administered by one college.3. A department offers several courses, each of which has a unique course name, a unique code number, a courselevel (Level: this can be coded as 1 for freshman level, 2…
Justify how the main key of a relation is connected to all of the qualities of the relationship in issue, as well as the functional dependencies that exist among all of those features.
You have been asked to design part of a database for a hospital. Thehospital managers provided you with an informal description of theirdata which you can find on the next page in Figure 1.(a) Provide an Enhanced Entity-Relationship Model for the hospital’s data model (Figure 1) in form of a diagram in Chen notation. Identify special attributes (including keys) in the diagramand include participation and cardinality constraint annotationsfor your relationships.List and explain any additional assumptions you think you haveto make because the specification was unclear or incomplete.Note that there should not be many such extra assumptions.Marking CriteriaThe following criteria will be used to mark your model:• extent to which your model is correct, minimal (no featuremodelled twice), complete and expressive• correct use of Chen notation (including special attributes)• correct cardinality and participation constraints (includingnotation)• readability and presentation of your answer. You…
Develop ERD (Entity Relationship Diagram) for one of the following two scenarios. NOTE: No further details were provided by the professor. The diagram can be drawn by hand also if preferred. Option1: A customer who goes to a grocery shop generates one invoice each time s/he goes to the grocery. Customer data to capture includes ID, FirstName, LastName, City, State, CreditLimit. An invoice should capture the data on InvoiceID, SaleDate, SalesPerson, and Amount. Provide a conceptual ERD for this transaction scenario. Option 2: One sports team consists of many players, and one player can belong to one sports team at a time. Sports team data should have TeamID, TeamName, CoachName, memberNumbers, City, State, owner, TeamValue. Each player can be represented with PID, Name, Address, PhoneNo, Position, weight, height, Value, and Scores. Provide a conceptual ERD to develop a database.
For the following collection of entities, give the assumptions concerning the relationship between entities. EMPLOYEE( EMP _CODE, EMP _LNAME, EMP _FNAME, EMP _INITIAL) BUILDING(BLDG_CODE, BLDG_NAME, EMP _CODE) ITEM(ITEM_ID, ITEM_DESCRIPTION, ITEM_ROOM, BLDG_CODE)