Closely examine the following tables and use it to answer the questions that follow: department course student_type student_category Student registration registration_type Suppose that the following are true: - all id column are primary keys in whichever table they are - the dept_id in course table is foreign key that is referencing the id column in the department table - the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columns in the student_type and student_category tables respectively. - the coursecode and stud_id columns in the registration table are foreign keys in the course and student tables respectively. 1. Design an Entity Relationship Diagram for the system above. 2. Write query that would return the names and contacts of all Local students. 3. Write a query that would return names, contact and locations of all departments that has a location text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”] 4. What would be the result returned for the following query: SELECT first_name, last_name, student_type.name, student_category.name FROM student, student_type, student_category WHERE stud_type_id = student_type.id AND stud_cat_id = student_category.id AND student_type.name = “Sandwich”; 5. Write a query that would present the first and last names, student type and category of all distance students. 6. What would be the result of the following query: SELECT student.id, first_name, last_name, contact, r
Closely examine the following tables and use it to answer the questions that follow: department course student_type student_category Student registration registration_type Suppose that the following are true: - all id column are primary keys in whichever table they are - the dept_id in course table is foreign key that is referencing the id column in the department table - the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columns in the student_type and student_category tables respectively. - the coursecode and stud_id columns in the registration table are foreign keys in the course and student tables respectively. 1. Design an Entity Relationship Diagram for the system above. 2. Write query that would return the names and contacts of all Local students. 3. Write a query that would return names, contact and locations of all departments that has a location text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”] 4. What would be the result returned for the following query: SELECT first_name, last_name, student_type.name, student_category.name FROM student, student_type, student_category WHERE stud_type_id = student_type.id AND stud_cat_id = student_category.id AND student_type.name = “Sandwich”; 5. Write a query that would present the first and last names, student type and category of all distance students. 6. What would be the result of the following query: SELECT student.id, first_name, last_name, contact, r
Principles of Information Systems (MindTap Course List)
13th Edition
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Ralph Stair, George Reynolds
Chapter12: System Acquisition And Development
Section: Chapter Questions
Problem 3PSE: A new sales ordering system needs a relational database that contains a customer table, a product...
Related questions
Question
100%
Closely examine the following tables and use it to answer the questions that follow:
department
course
student_type
student_category
Student
registration
registration_type
Suppose that the following are true:
- all id column are primary keys in whichever table they are
- the dept_id in course table is foreign key that is referencing the id column in the department table
- the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columns
in the student_type and student_category tables respectively.
- the coursecode and stud_id columns in the registration table are foreign keys in the course
and student tables respectively.
1. Design an Entity Relationship Diagram for the system above.
2. Write query that would return the names and contacts of all Local students.
3. Write a query that would return names, contact and locations of all departments that has a
location text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”]
4. What would be the result returned for the following query:
SELECT first_name, last_name, student_type.name, student_category.name
FROM student, student_type, student_category WHERE stud_type_id = student_type.id
AND stud_cat_id = student_category.id AND student_type.name = “Sandwich”;
5. Write a query that would present the first and last names, student type and category of all distance students.
6. What would be the result of the following query:
SELECT student.id, first_name, last_name, contact, registration_type.name, course.code
FROM student, registration, registration_type, course WHERE student.id = stud_id
AND reg_type_id = registration_type.id AND registration_type.name = "re-sit" AND course_code = course.code;
7. Write a query that would return the names and date of birth of all students that have registration_type of Normal.
8. Write a query that would return the first names, last names, contacts and student types of all sandwich students.
9. What would be the results of this query:
SELECT course_code, department.name department FROM course,registration, department
WHERE course.dept_id = department.id
AND course.code=course_code
GROUP BY course_code ORDER BY course_code desc;
10. Write a query that would return the departments names and the number of courses registered from them.
Transcribed Image Text:Closely examine the following tables and use it to answer the questions that follow:
department
+---
| location
| contact
student type
student category
id | name
id | name
id | name
Computer Science
Mathematics
Statistics
001
North Ave 1
+233234545453
ee2
North Ave 2
+233234564554
+233285475547
+233264638374
| +233244454744
1 | Regular
2 | Distance||
3 | Sandwich |
1 | Foreign
2 Local
003
North Ave 3
Sociology
e05 | History
e06 | English
Linguistic
e04
East Ave 4
East Ave 2
East Ave 3
+233245653443
ee7 |
East Ave 4
+233268888888
course
| title
dept_id | credit
code
CSIT205
CSIT206 | Applied IT Programming
CSIT207
DCIT103 | Office Productivity Tools
DCIT104 | Programming Fundamentals
HIST101 | Introduction to History
Object Oriented Techniques For IT Problem Solving
1
3
Database Fundamentals
3
1
3
5
3
HIST103
History of The Gold Coast
STAT101 | Introduction To Basic Statistics
3 |
3
Student
id
| first_name | last_name | dob
| contact
| stud_cat_id | stud_type_id
e0000001 | Stephen
Amankwah
Anekenu
Marvel
Nelson
1999-01-12
+233456789021
2
Favor
e0000003 |Anthony
eeee0004 |Abel
e0000005 | sSamuel
e00eee06 | Emmanuel
e0000007 | warrior
e00eeee8 | Abakok
eee00009 |Charles
e0000002
2001-02-13
+243456789021
2005-02-15
+123768875748
1
2003-04-27
+656534875748
1
Laryeh
Naagba
Nuku
Mensah
2000-07-24
+233208875748
1.
2002-06-07
+247654231565
2
2003-09-25
+727868875748
2
2
2007-09-3Ө
2 |
2 i
+876534875748
2
| Anane
2009-10-27
+234308875748
2
registration
registration_type
+-------
stud_id | semester | reg_date
| reg_type_id | course_code
id | name
1 | STAT101
1 | DCIT103
1| HIST101
1 | HIST103
2 | HIST101
1| CSIT205
1 | CSIT207
7
Sem 1
2020-01-13 ее:ее:ее
1 | Normal
2 | Re-sit
3 | Supplementary
1| Sem 1
1 | Sem 1
2021-04-23 09:35:47
2020-01-13 e0:00:00
1
Sem 1
2020-01-13 ее:ее:ее
1
Sem 1
2021-01-13 ee:e0:00
2
Sem 1
2021-01-13 ee:00:00
2 | Sem 1
2
2021-01-13 ee:00:00
Sem 1
2021-04-23 e9:35:47
DCIT103
7
Sem 1
2019-01-10 ee:00:00
1
DCIT103
1 | DCIT104
1| STAT101
7
Sem 1
2019-01-10 ee:e0:e0
7
Sem 1
2019-01-10 00:00:00
7 | Sem 1
7 Sem 1
7
2019-01-10 ee:00:00
1
HIST103
2019-01-10 ee:00:00
1
HIST101
Sem 1
Sem 1
2021-02-17 e0:00:00
1
CSIT205
7
2021-01-17 ee:00:e0
1| CSIT207
7
Sem 1
2019-01-10 ee:00:00
2
DCIT103
6| Sem 1
8 | Sem 1
2021-04-23 09:35:47
2
DCIT103
2021-04-23 e9:35:47
2
DCIT103
2 | DCIT103
2| DCIT103
1 | CSIT206
2 | CSIT207
4
Sem 1
2021-04-23 09:35:47
9
Sem 1
2021-04-23 09:35:47
3
Sem 1
2021-04-22 20:40:46
3
Sem 1
2021-04-22 20:41:01
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 2 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Principles of Information Systems (MindTap Course…
Computer Science
ISBN:
9781305971776
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Systems Architecture
Computer Science
ISBN:
9781305080195
Author:
Stephen D. Burd
Publisher:
Cengage Learning
Principles of Information Systems (MindTap Course…
Computer Science
ISBN:
9781285867168
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Principles of Information Systems (MindTap Course…
Computer Science
ISBN:
9781305971776
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Systems Architecture
Computer Science
ISBN:
9781305080195
Author:
Stephen D. Burd
Publisher:
Cengage Learning
Principles of Information Systems (MindTap Course…
Computer Science
ISBN:
9781285867168
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning