CIS 105 Assignment Schedule

STUDENT Name Student number Smith Brown COURSE Course_name Intro to Computer Science Data Structures Discrete Mathematics Database SECTION 17 8 102 112 119 135 Class 1 2 Course_number CS1310 CS3320 MATH2410 CS3380 Section_identifier Course_number 85 MATH2410 92 CS1310 CS3320 MATH2410 CS1310 CS3380 Major CS CS Credit_hours 4 4 3 3 Semester Fall Fall Spring Fall Fall Fall Year 07 07 08 08 08 08 Department CS CS MATH CS Instructor King Anderson Knuth Chang Anderson Stone

Table name: ADVISORS ADVISORID ADVISORNAME A102 Sami A103 Alice A104 Suad A105 Tamil A101 Arun Table name: DEPARTMENTS DEPTID DEPTNAME ELC D102 D101 IT D103 BSD D104 ENG Table name: STUDENTS STUDENTID STUDENTNAME DOB GENDER ALLOWANCE DEPTID ADVISORID STUDENTLEVEL AGE 6/1/1999 Male 12/30/1994 Female 6/1/1999 Male 5/6/1995 Male 10/15/1992 Male S103 Hisham 90 D102 A102 Level 1 22 S102 Sara 45 D103 A104 Diploma 27 S101 Salim 45 D103 A104 Diploma 22 S107 Khalil 45 D101 A101 BTech 26 S109 Said 45 D104 A101 Advanced Diploma 29 s100 Ahmad 5/1/1995 Male 45 D101 A103 BTech 26 S104 Faisal 4/5/1998 Male 90 D104 A101 Advanced Diploma 23 S105 Fatima 2/23/1992 Female 90 D102 A102 Level 2 29 9/12/1999 Female 8/13/1991 Female S106 Arwa 45 D102 A102 Level 3 22 S108 Ibtisam 90 D103 A104 Diploma 30 Write the SQL statement for the following: 1. Using the ON clause, display the advisor name and student name for all students in the BTech level

* with explanation

SQL QUESTIONS

The ABC Database Tables Table A TRUCK_NUM BASE CODE TYPE_CODE TRUCK_MILES TRUCK BUY DATE TRUCK SERIAL NUM 501 502 501 23-Sep-07 AA-322-12212-WM1 05-Feb-06 AC-342-22134-Q23 11-Nov-OS AC-445-78656-Z99 06-Jan-07 WVQ-112-23144-T34 01-Mar-06 FR-998-32245-W12 15-Jul-03 AD-456-00845-R45 17-Oct-04 AA-341-96573-284 07-Aug-05 DR-559-22169-D33 12-Feb-08 DE-887-98458-E94 1001 1032 1003 1004 1005 1006 1007 1009 1009 Table C Table B BASE CODE 501 502 Lexington 503 Cape Girardeau 504 Detton 503 501 502 502 T TYPE_CODE 503 BASE CITY Murfreesboro TYPE DESCRIPTION 1 Single box, double-sode 2 Single box, single-axde 3 Tandem trailer, single-exde 1 1 2 1 2 2 3 32 2 32123.5 76934.3 12346.6 2894.3 45673.1 193245.7 32012.3 44213.6 10932.9 BASE STATE BASE_AREA_CODE TN KY MO GA 615 568 456 901 BASE PHONE 123-4567 234-5678 345-6789 458-7890 BASE MANAGER Andree D. Gallager | George H. Delarosa Maria J. Talindo Peter F. McAvea

SQL DDL corresponding to this schema (i.e. the CREATE TABLE statements Database Schema:branch ( branch_name, branch_city, assets )customer ( ID, customer_name, customer_street, customer_city )loan ( loan_number, branch_name, amount )borrower ( ID, loan_number )account ( account_number, branch_name, balance )depositor ( ID, account_number )

Table name: ADVISORS ADVISORID ADVISORNAME A102 Sami A103 Alice A104 Suad A105 Tamil A101 Arun Table name: DEPARTMENTS DEPTID DEPTNAME D102 ELC D101 IT D103 BSD D104 ENG Table name: STUDENTS STUDENTID STUDENTNAME DOB GENDER ALLOWANCE DEPTID ADVISORID STUDENTLEVEL AGE Hisham 6/1/1999 Male 90 D102 S103 A102 Level 1 22 12/30/1994 Female 45 D103 S102 Sara A104 Diploma 27 S101 Salim 6/1/1999 Male 45 | D103 A104 Diploma 22 5107 Khalil 5/6/1995 Male 45 D101 A101 Blech 26 45 | D104 Advanced Diploma S109 Said 10/15/1992 Male A101 29 5/1/1995 Male 45 D101 90 D104 5100 Ahmad BTech Advanced Diploma A103 26 4/5/1998 Male 2/23/1992 Female 9/12/1999 Female S104 Faisal A101 23 0 D102 45 D102 S105 Fatima 90 A102 Level 2 29 S106 Arwa A102 Level 3 22 S108 Ibtisam 8/13/1991 Female 90 D103 A104 Diploma 30 Write the SQL statement for the following: 1. Using the ON clause, display the advisor name and student name for all students in the BTech level 2. Display the student name, gender and department name of…

EXPERIMENTAL PROCEDURE   Patients visit the hospital and their visit history is maintained by the hospital staff. Different physicians may be available on different dates. They diagnose and treat the patients of all categories. Some of treatments are free while others are to be paid by the patients. Sample data of the case is shown in the following chart.   Patient History Report PatientID Name Address Visit Date Physician Diagnosis Treatment P-100809 A City: X 12-02-2007 20-02-2007 29-02-2007 15-03-2007 Dr. Z Dr. F Dr. R Dr. L Chest Infection Cold Hepatitis-A Eyes Infection Free Free Paid Paid P-200145 N City: Y 10-01-2007 15-02-2007 25-03-2007 Dr. L Dr. K Dr. A Bone Fracture Cough Flu Paid Free Free     Task 1                     Draw a dependency diagram and transform the above data to first normal form by eliminating repeating groups such that each row in the relation is atomic. Be sure to create an appropriate name for the…

Schema Implementation( create table and insert )

Convert the following ER diagrams into relational schemas

Convert to normalization

GRADE REPORT STUDENT Name Student number Class Student number Section identifier Grade Major CS Smith 17 17 112 Brown 2 CS 17 119 85 92 COURSE 102 Course number Credit hours Department CS1310 Course name 135 Intro to Computer Science Data Structures Discrete Mathematice Database CS CS3320 4 CS MATH2410 MATH PREREQUISITE CS3380 CS Course number Prerequisite number CS3320 CS3380 CS3380 MATH2410 SECTION Section identifier Course number Semester Year Inatructor CS3320 CS1310 MATH2410 Fal King Anderson 85 07 02 CS1310 Fall 07 102 CS3320 Spring 08 Knuth 112 MATH2410 Fall 08 Chang 119 CS1310 Fal 08 Anderson 135 CS3380 Fall 08 Stone 4. Show the result of the following queries as it would apply to the database state given above. 1. SELECT DISTINCT Name FROM STUDENT WHERE Major="cs; 2. SELECT GRADE REPORT.Student number, SUM(Credit_hours), COUNT(") FROM GRADE_REPORT, SECTION,COURSE WHERE GRADE REPORT.Section_identifier= SECTION.Section_identifier AND SECTION.Course_number=COURSE.Course_number…

SQL Data Manipulation Database tables are: Hospital hospitalNo name city 1493 Cambridge St Boston capacity Cambridge Mount Sinai 2 East 101st St H117 600 H120 New York 700 ******** ******* ******* Doctor doctorNo salary hospitalNo specialty Cardiologist Neurologist name Robert Donald Xin Wang D201 180000 H117 D205 225000 H120 Patient | age phoneNo 617-482-5801 212-353-1463 patientNo name doctor P366 Arthur Gluck 72 D201 P351 Lisa Hershman 46 D205 ****

Database

ITCS285

Database name: Ch03_StoreCo Table name: EMPLOYEE EMP CODE EMP TITLE EMP LNAME EMP FNAME EP NTIAL EMP DOe STORE CODE imson John 21-May-64 Ratula 09-Feb-60 Nancy Lotte 2 Ms 3 Ms. Oreenboro R 02-Oct-61 4 Jervie Robert 01Jun-71 23-Nov-59 4 Mrs Runperstro SM. Smth 25-Dec-65 Renselser Ogalo Johneson Cary A 31--62 7 M 8 Ms Roberto 10-Sep-60 19-Apr-55 06-Ma6 21-Oct-72 Ekzbeth Endemar Jack 2. 10 Ms. Rose 11 M Broderick Ton 08-Sep 74 25-Au4 25May6 24-May-64 03-Sep-60 12-Nov.70 24 Jan-71 12 M Washington Alan Y 13 M Snth Peter IN 14 Ms Smth Sherry 15 M Olenko Howard 16 M Archiso Barry 17 Ms Grimaido Jenine 4. 18 M. Roserberg Andrew D 03-Oct-60 06-Mar-70 19 M Rosten Peter F 20 M Mckee Robert 1 21 Ma Baunann Jenniter A 11-Dec-74 Table name: STORE STORE YTD SALES REGION_CODE P CODE STORE CODE STORE NAME 1 Access anction 1003455 76 14217 39 986783 22 944568 56 2 Database Coner 3 Tuple Charge 4 Atribute Aley 5 Prinary Key Point 1 29300 45 15 Table name: REGION REGION CODE REOION DESCRPT 1 East 2 west…

Table: Task Primary key: TaskID Foreign key: Team MemberID TaskID 1 2 3 Description Build login screen Implement inventory management Add logo to splahs 1 screen Duration StartDate 2022-05-06 4 10 TeamMemberID 2 1 2022-05-15 1 2022-05-06 2 Write a SQL statement that will add the below row to the Task table. TaskID Description Duration StartDate Team MemberID 3 4 Implement 2022-05-07 email notifications Write a SQL statement that will change the value of the Task description with ID 3 to "Add logo to splash screen".

Processing a SQL query The marketing manager needs a list of all customers who placed a custom order exceeding a certain dollar amount during the previous year's 4th quarter holiday season: • Identify the DBMS component at each point in the path of the SQL query's process. 4 K= गु 2 1 3 7 6 5 Database RAM User

SQL

Answer the following questions by referring to the tables below. COURSE Course_name Course_number Credit_hours Department Intro to Computer Science CS1310 4 CS Data Structures CS3320 4 CS Discrete Mathematics MATH2410 3 MATH Database CS3380 3 CS   SECTION Section_ID Course_number Semester Year Instructor 85 MATH2410 Fall 07 King 92 CS1310 Fall 07 Anderson 102 CS3320 Spring 08 Knuth 112 MATH2410 Fall 08 Chang 119 CS1310 Fall 08 Anderson 135 CS3380 Fall 08 Stone Retrieve a list of all the courses under the "CS" department, listed alphabetically by course name. Retrieve the section ID, course name, course number, semester, year, and Instructor of the courses taught by Instructor "Anderson". Insert a tuple for a new COURSE with the following information: 4.The course name is Computer networks, and the course number is CS3480. It belongs to the CS…

Check the entities to be in the 3NF normal form

DCIT184 STAT181 Closely examine the following tables and use it to answer the questions that follow: department student_type student_category id |name | location | contact id name id name Computer Science Mathematics Statistics Sociology 005 | History English Linguistic North Ave 1 | +233234545453 North Ave 2 | +233234564554 North Ave 3 001 002 1 | Regular 2| Distance 3 | Sandwich 1 | Foreign 2| Local 003 +233285475547 004 East Ave 4 +233264638374 East Ave 2 +233244454744 006 East Ave 3 +233245653443 007 East Ave 4 +233268888888 course code | title dept_id | credit CSIT205 Object Oriented Techniques For IT Problem Solving Applied IT Programming Database Fundamentals Office Productivity Tools Programming Fundamentals Introduction to History History of The Gold Coast Introduction To Basic Statistics CSIT206 3 CSIT207 DCIT103 DCIT104 HIST101 5 | 3 HIST103 3 STAT101 Student id first_name last_name dob contact stud_cat_id | stud_type_id e0000001 Stephen Amankwah 1999-01-12 +233456789021 1…

2. For the below Un-normalized table, apply the normalization forms and list all the Normalized tables Patient Treatment Patient# Name Room# Treatment# Name 568694 Coyle Morphine 568694 Coyle 568645 David 568675 Katie 105 105 106 106 10223 10224 Tetracyclene 10223 Morphine 10225 Hydromorphone Dosage 10 mg/ml 05. mg/ml 10 mg/ml 2.5 ml Start Date Finish Date 3/24/20 4/24/20 3/24/20 1/15/20 2/20/20 4/17/20 2/5/20 3/10/20

mysql> CREATE TABLE School (    -> SchoolNumber INT PRIMARY KEY,    -> Name VARCHAR(50),    -> Address VARCHAR(100),    -> PhoneNumber BIGINT,    -> Built DATE,    -> Size INT    -> );ERROR 1046 (3D000): No database selectedmysql> SELECT * FROM School;ERROR 1046 (3D000): No database selected

Use the university schema for the following questions student takes ID ID course id sec id name dept_name tot ged semester vear grade section course course id sec id department course id advisor title dept_name credits dept name s id semester Year building room number time slot id building budget time_slot time slot id day start time end time prereq instructor classroom couTse id grereg id ID building room_number name dept_name salary сараcity teaches ID course id sec id semester year Figure 2.9 Schema diagram for the university database. Use the online SQLite tool from db-book.com to write SQL statements for each of the questions below. Paste your solution SQL statement under each question and upload the document to Canvas. 1. Display the name and total institution credits taken (not tot_cred which includes transfer credits) of students that do not have an assigned advisor (across all years). a. Hint: link takes with courses and student, for students not in advisor, project the name…

Use Mysql code The database has three tables for tracking horse-riding lessons:   Horse with columns: ID - primary key RegisteredName Breed Height BirthDate     Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress     LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key   Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results. Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.

Write Mysql code The database has three tables for tracking horse-riding lessons:   Horse with columns: ID - primary key RegisteredName Breed Height BirthDate     Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress     LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key   Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.