Over the past 5 weeks, you have learned all the steps to create and maintain a database. In this weeks Discussion, describe the organization that needs a database. Describe the purpose of the new database, how it will be used, the entities, and the business rules you will use to create this database.

Database Project

Identify your project to the class. Define the purpose, entities, and business rules.

Post a response. The initial response should be 400500 words with APA references

This above discussion is based on Assignment same data base and data should be used for above discussion

pls use same below assignment university database and data to answer above discussion

1. Create a University database, create 5 tables with Primary keys and foreign keys, insert data into all the tables, and display all the tables data

//Query to create Database

CREATE DATABASE University;

//Query to use University Database

USE University;

//Query to create Student table

CREATE TABLE Students( student_id INT NOT NULL, name VARCHAR(50) NOT NULL, branch VARCHAR(10), PRIMARY KEY(student_id) );

//Query to create Courses table

CREATE TABLE Courses( course_id INT NOT NULL, course_name VARCHAR(50), PRIMARY KEY(course_id) );

//Query to create Teachers table

CREATE TABLE Teachers( teacher_id INT NOT NULL, name VARCHAR(50), salary DECIMAL(10,2), course_id INT, PRIMARY KEY(teacher_id), FOREIGN KEY(course_id) REFERENCES Courses(course_id) );

//Query to create Tests table

CREATE TABLE Tests( test_id INT NOT NULL, test_name VARCHAR(50), total_marks INT, PRIMARY KEY(test_id) );

//Query to create Score table

CREATE TABLE Scores( score_id INT NOT NULL, student_id INT, course_id INT, marks INT, test_id INT, PRIMARY KEY(score_id), FOREIGN KEY(student_id) REFERENCES Students(student_id), FOREIGN KEY(course_id) REFERENCES Courses(course_id), FOREIGN KEY(test_id) REFERENCES Tests(test_id) );

//Query to insert data into Students table

INSERT INTO Students VALUES(1, "Student1", "CSE"), (2, "Student2", "Mech"), (3, "Student3", "CSE"), (4, "Student4", "Civil");

//Query to insert data into Courses table

INSERT INTO Courses VALUES(101, "Course101"), (102, "Course102"), (103, "Course103");

//Query to insert data into Teachers table

INSERT INTO Teachers VALUES(201, "Course101 Teacher", "8000", 101), (202, "Course102 Teacher", "7000", 102), (203, "Course103 Teacher", "10000", 103);

//Query to insert data into Tests table

INSERT INTO Tests VALUES(301, "Workshop", 200), (302, "Mains", 70), (303, "Practicals", 30);

//Query to insert data into Scores table

INSERT INTO Scores VALUES(401, 1, 101, 70, 301), (402, 2, 102, 75, 302), (403, 3, 103, 80, 303);

//Query to display data of Students, Courses, Teachers, Tests, and Scores

SELECT * FROM Students;

SELECT * FROM Courses;

SELECT * FROM Teachers;

SELECT * FROM Tests;

SELECT * FROM Scores;

2. Perform given SQL on above tables

1. Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk(*). State the purpose of the query; show the query and the output.

//Below Query will list out the all the Students with student_id, name, and branch where branch is CSE.

SELECT student_id, name, branch FROM Students WHERE branch="CSE";

2. Get information from at least 3 tables in one statement, and provide the output using the Join operator. Use ANSI Join syntax. State the purpose of the query; show the query and the output. Add a screen shot of SS Management Studio showing the query and results.

//Below Query will display the Marks of Student2 in Course102

SELECT Students.name, Students.branch, Courses.course_name, Scores.marks FROM Students JOIN Scores ON Students.student_id = Scores,student_id JOIN Courses ON Scores.course_id = Courses.course_id WHERE Students.student_id = 2 and Courses.course_id = 102;

3. Get information from 2 tables in one statement, and provide the output using the Left Outer Join operator. State the purpose of the query; show the query and the output. The outer join should be designed to retrieve information from the left table that has no matches in the right table. If that is not possible for your database, explain why.

//Below Query will display the marks of Students, if marks for a student doesn'e exist, marks will display as NULL

SELECT Students.name, Scores.marks FROM Students LEFT OUTER JOIN Scores ON Students.student_id = Scores.student_id;

4. Create a query using the IN keyword with a subquery. State the purpose of the query; show the query and the output.

//Below Query will list out all teachers and their salaries where their teaching course is there in the course id that we select in sub-query from Courses table.

SELECT name, salary FROM Teachers WHERE course_id IN (SELECT course_id FROM Courses);

5. Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command. State the purpose of the query; show the query and the output.

//Below Query displays the COUNT of students in particular branch

SELECT COUNT(student_id), branch FROM Students GROUP BY branch;

6. Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command using the HAVING clause to filter the aggregate results. State the purpose of the query; show the query and the output.

//Below Query displays the COUNT of students in particular branch only when number of students persuing the course is grater than 1

SELECT COUNT(student_id), branch FROM Students GROUP BY branch HAVING COUNT(student_id) > 1;

7. Update one row. State the purpose of the query; show the result set for the row(s) before the update; show the query; show the row(s) after the update.

//Below Query will update the Salary of teacher to 15000 where course_id = 102

//Before update SELECT * FROM Teachers;

//Update UPDATE Teachers SET salary = "15000" WHERE course_id = 102;

//After Update SELECT * FROM Teachers;

8. Delete one row. State the purpose of the query; show the result set before the delete; show the query; show the result set after the delete.

//Below Query delets the Test row where test_id is 303

DELETE FROM Tests WHERE test_id = 303;