IT 214: S2 Module.

Continue the SQL command to add data to an entity with the name INSTRUCTOR. Write the name of the SQL commands in upper cases. Complete only the part marked with Department: Id: 101 Name: Computer Science Phone 899-012.7638 • Median salary: 130.245.50

('AX765' , 'John', NULL, 'Doe', '1980-04-07' , 101)

Given the following tables: CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY, DNAME VARCHAR(50) NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE, LAUNCH_DATE DATE, MEDIAN _SALARY DECIMAL(8,2); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY, FIRST NAME VARCHAR(35) NOT NULL, MIDDLE NAME VARCHAR(35), LAST_NAME VARCHAR(35) NOT NULL, DOB DATE NOT NULL, DEPARTMENT_ID INT NOT NULL, FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); Finish the SQL command with the condition clause if needed to show a list of full names for the instructors having the first name 'John' and their department names. For the full name combine the first name and last name separated by space. Ignore middle names. Use the heading "Instructor" for the full name. Use the heading "Department" for the department name column. Complete only the part marked with Use upper case for SQL keywords SELECT CONCAT(FIRST_NAME, '', LAST_NAME) AS 'Instructor", DNAME AS 'Department FROM DEPARTMENT, INSTRUCTOR WHERE DEPARTMENT_ID=ID

AND FIRST_NAME='John'

Primary Key Dependencies To check this is a correct PK. we will need to identity all PK dependencies (shown above the dependency disgram) Idently the first five PK dependences, based on the order of the attributes in the table

Al the attributes must be determined by Uvi PK. So. the frst 5 attobutes that will have PK dependency, are the first five attributes in the table, except the ones already in the PK HOSPITAL_CODE, HOSPITAL_NAME, HOSPITAL_PHONE: HOSPITAL_EMAIL 000 DOCTOR_FIRST NAME. We skp DOCTOR_ID because is already in the PK

CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY, DNAME VARCHAR(50) NOT NULL UNIQUE, PHONE CHAR(12) NOT NULL UNIQUE, LAUNCH_DATE DATE MEDIAN_SALARY DECIMAL(8,2)); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY, FIRST_NAME VARCHAR(35) NOT NULL, MIDDLE NAME VARCHAR(35), LAST_NAME VARCHAR(35) NOT NULL, DOB DATE NOT NULL, DEPARTMENT_ID INT NOT NULL, FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); Finish the SQL command with the condition clause if needed to show a list of full names for all instructors and their department names. For the full name combine the first name and last name separated by space. Ignore middle names. Use the heading "Instructor" for the full name. Use the heading "Department" for the department name column. Complete only the part marked with Use upper case for SQL keywords ., DNAME AS 'Department' SELECT FROM DEPARTMENT, INSTRUCTOR WHERE DEPARTMENT_ID=ID;

CONCAT(FIRST_NAME, '', LAST_NAME AS 'Instructor"

Finish the SOL command with the condition clause if needed to show a list of the first middle and last names for all instructors and their department names. Use the heading "Department" for the department name column. Complete coly the part marked with Use upper case for SQL keywords SELECT FIRST NAME, MIDDLE_NAME, LAST _NAME._ FROM DEPARTMENT, INSTRUCTOR WHERE DEPARTMENT_ID=ID:

DNAME AS 'Department'

Given the following tables: CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY, NAME VARCHAR(50) NOT NULL UNIQUE, PHONE CHAR(12) NOT NULL UNIQUE, LAUNCH_DATE DATE, MEDIAN SALARY DECIMAL(8,2) ); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY, FIRST_NAME VARCHAR(35) NOT NULL, MIDDLE_NAME VARCHAR(35). LAST_NAME VARCHAR(35) NOT NULL, DOB DATE NOT NULL, DEPARTMENT_ID INT NOT NULL, FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); Finish the SQL command with the condition clause if needed to show a list of the first, middle and last names for all instructors and their department names. Use the heading "Department" for the department name column. Complete only the part marked with Use upper case for SQL keywords. SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, FROM DEPARTMENT, INSTRUCTOR WHERE DEPARTMENT_ID=ID;

DNAME AS 'Department'

Identify the next four partial dependencies in the order of the attributes in the table (select none if no more dependencies): [A11] determines [A12] [A21] determines [A22] [A31] determines [A32] [A41] determines [A42] Are there more partial dependencies (more/none)? [M]

DOCTOR_ID determines DOCTOR_FIRST_NAME DOCTOR_ID determines DOCTOR_LAST_NAME DOCTOR_ID determines DOCTOR_PHONE DOCTOR_ID determines DOCTOR_EMAIL There are no more partial dependencies because for a given DOCTOR_ID, there will be several appointments and patients, and for a given date, or time, or sate/time combination, there are several hospitals, doctors, locations and patients

Partial Dependencies Identity the first four partial dependencies millie pitter of Hie astributes in the table (select none if no moce dependences) [A11) determines (A12] [A21] determines [A22) [A31] determines [A32] [A21) determines [A42) Are there more partial dependencies more/none? (M)

DOCTOR_ID determines HOSPITAL_CODE DOCTOR_ID determines HOSPITAL_NAME DOCTOR_ID determines HOSPITAL_PHONE DOCTOR_ID determines HOSPITAL_EMAIL There are more partiel dependencies because for a given DOCTOR ID, there will be a single corresponding DOCTOR. FIRST_NAME. DOCTOR_LAST_NAME DOCTOR PHONE and DOCTOR _EMAIL

Given the table: DEPARTMENT ID (INT), DNAME (VARCHAR(50)), PHONE (CHAR(12)), LAUNCH_DATE (DATE), MEDIAN_SALARY (DECIMAL(8,2))) Continue the SQL command to create an entity with the name INSTRUCTOR. Write the reserved words of the SQL commands in upper cases. Complete only the part marked with the command: Do not include the comma separator in your answer. Write only the needed features of the attributes (columns) in alphabetical order Specification: An instructor has a first name, which has up to 35 characters. SQL Command: CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY,

FIRST_NAME VARCHAR(35) NOT NULL

Given the following tables: CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY, DNAME VARCHAR(50) NOT NULL UNIQUE, PHONE CHAR(12) NOT NULL UNIQUE, LAUNCH_DATE DATE MEDIAN_SALARY DECIMAL(8,2)); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY, FIRST_NAME VARCHAR(35) NOT NULL, MIDDLE_NAME VARCHAR(35), LAST _NAME VARCHAR(35) NOT NULL, DOB DATE NOT NULL, DEPARTMENT_ID INT NOT NULL, FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); Continue the SQL command with the table clause to show a list of the first, middle and last names for all instructors and their department names. Complete only the part marked with Use upper case for SQL keywords SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, DNAME

FROM DEPARTMENT, INSTRUCTOR

Transitive Dependencies: Identify the first four transitive dependencies in the order of the attributes in the table (select none if no more dependencies):

HOSPITAL_CODE. For a given HOSPITAL_CODE, there will be a single corresponding HOSPITAL_ NAME, HOSPITAL_PHONE, and HOSPITAL _EMAIL. One may assume thatt the name, phone and email are also unique for a hospital, so, the name will determine the code as well, and so on. However, they are equivalent dependencies, so we do not need to specify them. HOSPITAL_CODE determines HOSPITAL_NAME HOSPITAL_CODE determines HOSPITAL_PHONE HOSPITAL_CODE determines HOSPITAL_EMAIL Next, we skip over DOCTOR_ID because is part of the primary key. The doctor name is not determining anything as it is possible to have two doctors with the same name. The doctor phone and email might or might be not unique for a given doctor. However, even if they are unique, they are equivalent dependencies with those from DOCTOR_ID Next, we skip over APPOINTMENT_DATE and APPOINTMENT_TIME because thet are part of the primary key. A given APPOINTMENT SUITE will have the same APPOINTMENT_DIRECTIONS, as we know that all hospitals a

Entity Identification: Identify the main needed entities specified above, in the order they are introduced above, ignoring not-specified bridges or single instance entities. Type "none" or "" if there are no more entities to identify. Entity 1: [E1] Entity 2: [E2] Entity 3: [E3] Entity 4: [E4] Entity 5: [E5] Entity 6: [E6] Entity 7: [E7]

Not an entity: LEAGUE because is at most a single instance entity, as the database is for a given league. Entity 1: SEASON as we need to keep information about each season, like season name (FALL), season year (2020), registration starting date and ending date and also a season starting date and ending date. Entity 2: DIVISION as we need to keep information about each division, like season, age, sex, rank. Entity 3: CLUB because we need to keep information about each club, like name, contact email and phone. Entity 4: TEAM because we need to keep information about each team, as name, birth year, sex. Entity 5: MANAGER because we need to keep information about team managers, like name, phone, email. Entity 6: - Entity 7: -

Specify for the following attributes if they are required attributes (REQ) or optional attributes (OPT) by writing REQ or OPT in the table below.

OPT, OPT, REQ, REQ, OPT, REQ, REQ, REQ. REQ

Relationship Type Justification Complete the justification for the type of each relabonships idented Relationship 1: SEASON contains DIVISION Relationship type justificaton: [R1A) season contains [R18) division(3) and [R1C) dvision belongs to [R1D) s0a500(3) Needs bridge (R1E) Relationship 2: CLUB has member TEAM Relationship type justification: [RA) club has [R28) member teams) and [R2C) team belongs to [R20) club(s) Needs bridge [R2E) Relationship 3: TEAM is managed by MANAGER Relationship type justification (RJA] team is managed by (R38) mansoer(s) end [RJC) manager manages (R3D) team(s) Noeds bodge [R3E) Relationship 4: TEAM is assigned to DIVISION Relationship type justification: [R4A) seam is assugned to [R48) dvision(s) and [R4C) dvision has (R4D) assigned beam(s) Needs bridge (R$B)

Relationship 1: SEASON contains DIVISION Relationship 2: CLUB has member TEAM Relationship 3: TEAM is managed by MANAGER Relationship 4: TEAM Is assigned to DIVISION

Identify the first five attributes of the supertype, in the order they are introduced above. Do not include any surrogate primary key. Include the type discriminator for disjoint hierarchies, only after all the other attributes are identified. Analyze and correctly represent the composite and multi-value attributes, as needed. Type "none" or "" if there are no more entities to identify. Attribute 1: [A1] Attribute 2: [A2] Attribute 3: [A3] Attribute 4: [A4] Attribute 5: [A5]

The first common attribute is the name for all doctors, nurses and staff. This is a composite attribute having a first name and last name specifid. Because we want to attributes. process the name based on the first and last name, we will decompose the attribute. Therefore we will define FIRST_NAME and LAST_NAME as first two common The third common attribute is SSN. The fourth common attribute is PHONE. The next attribute is the type discriminator TYPE (or EMPLOYEE_TYPE). The other attributes are specific to a given subtype, like email only for doctors or role only for staff.

Hierarchy Identification: Identify the entities from the hierarchy specified above, in the order they are introduced above, ignoring not-specified bridges or single instance entities. Type "none" or "•" if there are no more entities to identify. Super-entity: [E1] Sub-entity: [E2] Sub-entity: [E3] Sub-entity: [E4] Sub-entity: [E5]

The super-entity is EMPLOYEE. This is the best generalization of the entities specified: doctor, nurse, staff. While the customer does not name this entity as such, common sense generalization supports it. The first subtype of employee that is described is a DOCTOR. We store it as a subtype because a doctor will have appointments while nurses or staff do not, The second subtype of employee that is described is a NURSE. We store it as a subtype because we keep which patients they prepared during the visits The third subtype of employee that is described is STAFF. We store it as a subtype becauses for a staff we keep the role they have. There are no other entities in the hierarchy.

Designer: May you show me some sample values for the model of the cars,like: 2020 Chevrolet Suburban LT? Customer: Sure, here are a few more car models we sell: • 2020 Chevrolet Silverado 1500 WT; • 2020 Chevrolet Bolt EV Premier, • 2019 Chevrolet Malibu LT 1LT; Which is the name you will give to the attribute representing such values?

VEHICLE_MODEL

CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY, DNAME VARCHAR(50) NOT NULL UNIQUE, PHONE CHAR(12) NOT NULL UNIQUE, LAUNCH DATE DATE, MEDIAN SALARY DECIMAL (82) ): CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY, FIRST_NAME VARCHAR(35) NOT NULL, MIDDLE NAME VARCHAR(35), LAST_NAME VARCHAR(35) NOT NULL, DOB DATE NOT NULL, DEPARTMENT_ID INT NOT NULL, Question 17 Given the following tables: FOREIGN KEY(DEPARTMENT _ID) REFERENCES DEPARTMENT(ID)); Finish the SQL command with the condition clause if needed to show a list of the first, middle and last names for all instructors and their department names. Complete only the part marked with Use upper case for SQL keywords. SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, DNAME FROM DEPARTMENT, INSTRUCTOR

WHERE DEPARTMENT_ID=ID;

Prefix the name of the attributes with the most specific entity they reflect. First five attributes are: HOSPITAL_CODE, HOSPITAL_NAME, HOSPITAL_PHONE, HOSPITAL_EMAIL, DOCTOR_ID, DOCTOR_FIRST_NAME, DOCTOR_LAST _NAME, DOCTOR _PHONE, DOCTOR _EMAIL, APPOINTMENT _DATE, APPOINTMENT_TIME Identify the next six attributes, in the order they appear in the data: Attribute 12: [A12] Attribute 13: [A13] Attribute 14: [A14] Attribute 15: [A15] Attribute 16: [A16] Attribute 17: [A17]

We continue the identification of the appointment attributes. The suite is specified next (APPOINTMENT_SUITE) and brief directions (APPOINTMENT_DIRECTIONS). Because there is no source of confusion, and to keep the names short, we will use SUITE instead of SUITE NUMBER and DIRECIONS instead of BRIEF_DIRECTIONS. The last attributes are related to the patient. We start with the id (PATIENT_ID). Because, we want to search based on the last name, the name will be divided in first name and last name (PATIENT_FIRST _NAME, PATIENT_LAST_NAME). The last attribute for a patient is the phone (PATIENT_PHONE).

Entity EMPLOYEE Analysis Attributes. ID (PK), FIRST_NAME, LAST_NAME, SSN, PHONE, TYPE Entity DOCTOR Analysis: Attribute identification Identify the first five attributes of the subtype, starting with the PK, then in the order they are introduced above Do not include any surrogate primary key. Analyze and correctly represent the composite and multi-value attributes, as needed. Type "none" or " " if there are no more entities to identify.

We will start with the PK which must be the PK of the supertype: EMPLOYEE_ID, which is a foreign key referencing ID in EMPLOYEE. The second attribute is EMAIL that we keep only for doctors. The third attribute is SPECIALTY that we keep only for doctors. The Fourth attribute is SCHOOL that we keep for doctors. Also, nurses will have a nurse school, but there is no need to unify those two in the supertype There are no more attributes specified