Create a view that finds the student name, Database Management System

Assignment Help:

Section A: Use the following tables to create a database called College. Use SQL commands.

Student

stuid(primary)	lastName	firstName	major	credits
S1001	Smith	Tom	History	90
S1002	Chin	Ann	Math	36
S1005	Lee	Perry	History	3
S1010	Burns	Edward	Art	63
S1013	McCarthy	Owen	Math	0
S1015	Jones	Mary	Math	42
S1020	Rivera	Jane	CSC	15

Faculty

facid (primary)	name	department	rank
F101	Adams	Art	Professor
F105	Tanaka	CSC	Instructor
F110	Byrne	Math	Assistant
F115	Smith	History	Associate
F221	Smith	CSC	Professor

Class

classNumber (primary)	facid	schedule	room
ART103A	F101	MWF9	H221
CSC201A	F105	TuThF10	M110
CSC203A	F105	MThF12	M110
HST205A	F115	MWF11	H221
MTH101B	F110	MTuTh9	H225
MTH103C	F110	MWF11	H225

Enroll

stuid	classNumber	grade
S1001	ART103A	A
S1001	HST205A	C
S1002	ART103A	D
S1002	CSC201A	F
S1002	MTH103A	B
S1010	ART103A
S1010	MTH103C
S1020	CSC201A	B
S1020	MTH101B	A

1. Create the database College.

2. Create the table Student.

3. Create the table Faculty.

4. Create the table Class.

5. Create the table Enroll.

6. Create all foreign keys

Section B: Using the database in Section A. Answer all questions.

7. Create a view that finds the student name, major and enrolled in the art class

8. Create a view that finds the student name, and classes enrolled

9. Create a stored procedure that finds the name of faculty and their schedule

10. Create a stored procedure that finds the student names for a particular course.

11. Create a role called students; give SELECT permission. Use a cursor to add all students as members of the above. Setup each student as a user with temporary password of first four letter of last name and add '8888'.

12. Convert to XML the student table.

Section C: Using the Halloween database.

13. Create a table called ProductImages which has the following fields ImageID (int, primary key, identity), productid (varchar), and ImageProduct (varbinary(max)).

Related Discussions:- Create a view that finds the student name

What is a data dictionary, What is a data dictionary? A data dictionar...

What is a data dictionary? A data dictionary is a data structure which keeps Meta data about the structure of the database i.e. the schema of the database.

#normalisation.., define normalisation and functional dependency

define normalisation and functional dependency

Explain the systems development life cycle, Explain the systems development...

Explain the systems development life cycle. This is the traditional method to develop, maintain, and replace information systems that contains the project identification and se

A Biological Database on Viruses, How to create a biological database based...

How to create a biological database based on viruses. How to decide which attribute should be there in the table and schema? How to identify the primary keys, foreign keys, domain,

Define cascading rollback, Define Cascading rollback The phenomenon whe...

Define Cascading rollback The phenomenon where a single transaction failure leads to a series of transaction rollbacks is known as Cascading rollback.

Copy data from one database to another database, Copy Data from One Databas...

Copy Data from One Database to another Database Project Description: I am having two MySQL databases with same tables. I just need to copy one Data from one to another dat

CMIS 420 Homework Assignment 2, describe all the tables you have created. ...

describe all the tables you have created. ?get table_name, status, num_rows, user_stats in user_tables system view ?get table_name, constraint_name, constraint_type in user_constr

Enforce referential integrity, Use Access 2007 to create a database calle...

Use Access 2007 to create a database called UniLib.mdb. - this file should be created on your personal drive at the university (or C: if you're using Access 2007 at home) and t

Create the logical database schema, 1. Based on the dependency diagram, cr...

1. Based on the dependency diagram, create a database whose tables are in at least 2NF, showing the dependency diagram for each table (as shown in class). Label the types of depe

Write query to list the staff who work within the branch, Consider the foll...

Consider the following relations: BRANCH( bno, street, area, city, pcode, Tel_no, Fax_no) STAFF( Sno, Fname, Lname, address, position , salary, bno) Express the subsequent qu

Write Your Message!

Name

Email id

Message

Verfication Code

User Account

All Pages