Using SQL, write Data Definition Language commands to set up the database schema for an Admission...

    

Transcribed Image Text:

Using SQL, write Data Definition Language commands to set up the database schema for an Admission Database System for a University with the tables below: a. STUDENT(SD, sName, GPA, sizeHS) b. COLLEGE(CName, state, enrollment) c. Apply(sID Name, major, decision) Hint: i. slis unique and not null for each Student Meme is unique and not null for each College ii. iii. slp. SName and major are unique for each record in the Apply table iv. GPA is a decimal value with at least 2 decimal points Research (over google) the SQL syntax of the SQL "INSERT INTO" statement and determine the commands to populate the tables with data below. COLLEGE cName Stanford Berkeley MIT Cornell STUDENT SID 123 234 345 456 567 678 789 987 876 765 654 543 sName Amy Bob Craig Doris Edward Fay Gary Helen Irene Jay Amy Craig state CA CA MA NY 3.9 3.6 GPA sizeHs 1000 1500 500 1000 3.5 3.9 enrollment 15000 36000 2.9 3.8 3.4 3.7 3.9 2.9 3.9 3.4 10000 21000 2000 200 800 800 400 1500 1000 2000 Apply SID 123 123 123 123 234 345 345 345 345 678 987 987 876 876 876 765 765 765 543 cName Stanford Stanford Berkeley Cornell Berkeley MIT Cornell Cornell Cornell Stanford Stanford Berkeley Stanford MIT MIT Stanford Cornell Cornell MIT major CS EE CS EE Biology Bioengineering Bioengineering CS EE History CS CS CS Biology marine biology History History Psychology CS decision Y N Y Y N Y N Y N Y Y Y N Y N Y N Y N Case Study 2: You are provided with the specification requirements below to implement a database management system (DBMS) for "Bank X" to store details about their Branches, Accounts, and Customers. SR-1: A BRANCH has a Branch that is numeric, a Branch Name, and Branch Address. The BranchJP.is unique for each branch while Branch Name and Branch Address may not be unique. SR-2: SR-3: CUSTOMER has an SSN field that is of numeric characters, along with Name, Phone, Address. SR-4: A Branch has multiple accounts linked to it, while an account can be linked to just one branch. A customer can possess no more than 4 accounts in the same branch but of different account types. While accounts are not shared between customers. SR-5: An ACCOUNT has an Account No field in numeric characters, Account Type, and Balance. The ACCOUNT_TYPE is either Savings, Checking, Credit Card, or Mortgage. The Account No is unique for each account. SR-6: A customer can have multiple accounts in different branches. SR-7: Only the customer's phone number may be empty (NULL), all fields in the database are mandatory. 3. Using SQL, write Data Definition Language (DDL) commands to set up the database schema with the below tables. Note: Include a primary key for each table: a. BRANCH (Branship. BranchNe BrensbAddress) b. ACCOUNT (ACcNo, AccTypeID, Branch, SSN, Balance) C. ACCOUNT_TYPE (ASGTvesIP. AccountType) d. CUSTOMER (SSN, Name, Phone, Address) 4. Using SQL, write Data Manipulation Language (DML) commands to populate the tables with data to illustrate SR4 to SR7. Using SQL, write Data Definition Language commands to set up the database schema for an Admission Database System for a University with the tables below: a. STUDENT(SD, sName, GPA, sizeHS) b. COLLEGE(CName, state, enrollment) c. Apply(sID Name, major, decision) Hint: i. slis unique and not null for each Student Meme is unique and not null for each College ii. iii. slp. SName and major are unique for each record in the Apply table iv. GPA is a decimal value with at least 2 decimal points Research (over google) the SQL syntax of the SQL "INSERT INTO" statement and determine the commands to populate the tables with data below. COLLEGE cName Stanford Berkeley MIT Cornell STUDENT SID 123 234 345 456 567 678 789 987 876 765 654 543 sName Amy Bob Craig Doris Edward Fay Gary Helen Irene Jay Amy Craig state CA CA MA NY 3.9 3.6 GPA sizeHs 1000 1500 500 1000 3.5 3.9 enrollment 15000 36000 2.9 3.8 3.4 3.7 3.9 2.9 3.9 3.4 10000 21000 2000 200 800 800 400 1500 1000 2000 Apply SID 123 123 123 123 234 345 345 345 345 678 987 987 876 876 876 765 765 765 543 cName Stanford Stanford Berkeley Cornell Berkeley MIT Cornell Cornell Cornell Stanford Stanford Berkeley Stanford MIT MIT Stanford Cornell Cornell MIT major CS EE CS EE Biology Bioengineering Bioengineering CS EE History CS CS CS Biology marine biology History History Psychology CS decision Y N Y Y N Y N Y N Y Y Y N Y N Y N Y N Case Study 2: You are provided with the specification requirements below to implement a database management system (DBMS) for "Bank X" to store details about their Branches, Accounts, and Customers. SR-1: A BRANCH has a Branch that is numeric, a Branch Name, and Branch Address. The BranchJP.is unique for each branch while Branch Name and Branch Address may not be unique. SR-2: SR-3: CUSTOMER has an SSN field that is of numeric characters, along with Name, Phone, Address. SR-4: A Branch has multiple accounts linked to it, while an account can be linked to just one branch. A customer can possess no more than 4 accounts in the same branch but of different account types. While accounts are not shared between customers. SR-5: An ACCOUNT has an Account No field in numeric characters, Account Type, and Balance. The ACCOUNT_TYPE is either Savings, Checking, Credit Card, or Mortgage. The Account No is unique for each account. SR-6: A customer can have multiple accounts in different branches. SR-7: Only the customer's phone number may be empty (NULL), all fields in the database are mandatory. 3. Using SQL, write Data Definition Language (DDL) commands to set up the database schema with the below tables. Note: Include a primary key for each table: a. BRANCH (Branship. BranchNe BrensbAddress) b. ACCOUNT (ACcNo, AccTypeID, Branch, SSN, Balance) C. ACCOUNT_TYPE (ASGTvesIP. AccountType) d. CUSTOMER (SSN, Name, Phone, Address) 4. Using SQL, write Data Manipulation Language (DML) commands to populate the tables with data to illustrate SR4 to SR7.

Answer rating: 100% (QA)

Answer Insert data into COLLEGE table INSERT INTO COLLEGE CName state enrollment VALUES ... View the full answer