The aim of assignment 1 is to implement a database schema and execute different types of SQL queries in Oracle SQL developer. Given the following relational schema: Student (studid, FirstName, LastName, Email, phoneNumber, DateofBirth, GPA) Club (clubid, ClubName, #studid) MemberOf (#clubid, #studid, joiningDate ) Activities (actid, actdt, place, durationNbHour) Organize (#actid, #clubid , fee) 1. Write SQL queries to create the following tables according to the below descriptions. Table name: Student studid Char(9) Primary key FirstName Varchar2(50) NOT NULL LastName Varchar2(50) NOT NULL Email Varchar2(50) NOT NULL, UNIQUE phoneNumber Number(8) NOT NULL, UNIQUE DateofBirth Date NOT NULL GPA Number(1,2) NOT NULL Table name: Club clubID Number(3) Primary key ClubName Varchar2(20) NOT NULL NOT NULL, UNIQUE, Foreign key studid Char(9) Table Name: MemberOf ClubID Number(3) Primary Key, Foreign Key Studid |Char(9) Primary Key, Foreign Key joiningDate Date Table Name: Activities Actid Number(3) Primary Key Actdt DATE NOT NULL Place Varchar2(50) NOT NULL durationNbHour Number NOT NULL Table Name: Organize Number(3) Primary Key, Foreign key actid IclublD Number(3) Primary key, Foreign key Fee Number(4,2)

The aim of assignment 1 is to implement a database schema and execute different types of SQL queries in Oracle SQL developer. Given the following relational schema: Student (studid, FirstName, LastName, Email, phoneNumber, DateofBirth, GPA) Club (clubid, ClubName, #studid) MemberOf (#clubid, #studid, joiningDate ) Activities (actid, actdt, place, durationNbHour) Organize (#actid, #clubid , fee) 1. Write SQL queries to create the following tables according to the below descriptions. Table name: Student studid Char(9) Primary key FirstName Varchar2(50) NOT NULL LastName Varchar2(50) NOT NULL Email Varchar2(50) NOT NULL, UNIQUE phoneNumber Number(8) NOT NULL, UNIQUE DateofBirth Date NOT NULL GPA Number(1,2) NOT NULL Table name: Club clubID Number(3) Primary key ClubName Varchar2(20) NOT NULL NOT NULL, UNIQUE, Foreign key studid Char(9) Table Name: MemberOf ClubID Number(3) Primary Key, Foreign Key Studid |Char(9) Primary Key, Foreign Key joiningDate Date Table Name: Activities Actid Number(3) Primary Key Actdt DATE NOT NULL Place Varchar2(50) NOT NULL durationNbHour Number NOT NULL Table Name: Organize Number(3) Primary Key, Foreign key actid IclublD Number(3) Primary key, Foreign key Fee Number(4,2)

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Q: Display the list of students who are involved in the IT club Display the details of all the…

A: First get the studid from Club table and then get those Student details This can be done using inner…

Q: Given the default schema of a database: Orders 123 Orderld OrderDate ADC OrderNumber 123 Customerld…

A: A Foreign key is a field in a table that is used to reference the Primary key of another table. It…

Q: Material : Relational Algebra Course : Database Systems Create a Relational Algebra From Entity…

Q: rite the SQL statements to create all of the necessary tables to represent the above diagram,…

A: Note: Since you have not provided the database name to write the SQL query so I am using the MYSQL…

Q: Given the default schema of a database: Orders 123 Orderld OrderDate ADC OrderNumber 123 Customerld…

A: Please refer to the following step for the complete solution to problem above.

Q: SOt block that uses a searched case statement to classity students based on their scores in a…

A: Answer in step2

Q: Please look at this schema. Please convert this into a relational schema (you can give the answer as…

A: Solution- Data Model Diagram to Relational Database Conversion Rules: 1. Each entity in Data Model…

Q: Choose a database application with which you are familiar. Design a schema and show a sample…

A: Design a schema and show a sample database for that application, using the notation of Figure 1.…

Q: Seminar eminar ID Integer eminar Date Date Seminar Time Time ocation Seminar Title Address String…

A: As the customer may or may not attend the seminar or several seminars, the seminar and the customer…

Q: What features do normalization procedures that use functional dependencies have?

A: Functional reliance: Between two characteristics—primary key attributes and non-key attributes…

Q: The following three tables make up a simple reservation system for a small campground. The database…

A: ER Diagram: ER diagram is defined as the interconnection of entities and attributes how they are…

Q: You have been tasked with creating a database solution for a local library. This database will be…

A: ERD or Entity-Relationship Diagram, is the visual representation of the structure and relationships…

Q: Write the SQL code that will list all of the drivers in the database that have been issued tickets.…

A: We will use the MySQL codes to find all the data asked in the question.

Q: Flight-Distance Database FLIGHT (Flight#, Origin, Destination, Mileage) FLIGHT Flight# DL507 DL123…

A: For a table to be in 3rd NF : It should be in 2nd NF and should not have any transitive dependency…

Q: Employee and Restaurant are two tables in a relational database: Employee (StaffNum, Name, WorksIn)…

A: The SQL queries for creating the tables and inserting data is given below First Create Restaurant…

Q: The main aim of your project is to create a database schema consists of 3 tables suggested by…

A: We have to create a database schema consisting of 3 tables suggested byyourself and simulate it…

Q: Problem 82 Write a query to display the lowest book cost in the system (Figure P7.82).

A: Aggregate Functions: SQL has some built-in functions and they are called as aggregate functions.…

Question

The aim of assignment 1 is to implement a database schema and
execute different types of SQL queries in Oracle SQL
developer.
Given the following relational schema:
Student
(studid,
FirstName,
LastName,
Email,
phoneNumber, DateofBirth, GPA)
Club (clubid, ClubName, #studid)
MemberOf (#clubid, #studid, joiningDate )
Activities (actid, actdt, place, durationNbHour)
Organize (#actid , #clubid , fee)
1. Write SQL queries to create the following tables
according to the below descriptions.
Table name: Student
studid
|Char(9)
Primary key
FirstName
Varchar2(50) NOT NULL
LastName
Varchar2(50) NOT NULL
Email
Varchar2(50) NOT NULL, UNIQUE
phoneNumber
Number(8) NOT NULL, UNIQUE
DateofBirth
Date
NOT NULL
GPA
Number(1,2) NOT NULL
Table name: Club
clubID
Number(3) Primary key
ClubName
Varchar2(20)NOT NULL
NULL, UNIQUE,
NOT
Foreign key
studid
Char(9)
Table Name: MemberOf
ClubID
Number(3) Primary Key, Foreign Key
Studid
Char(9)
Primary Key, Foreign Key
joiningDate
Date
Table Name: Activities
Actid
Number(3) Primary Key
Actdt
DATE
NOT NULL
Place
Varchar2(50)INOT NULL
durationNbHour Number
NOT NULL
Table Name: Organize
actid
Number(3) Primary Key, Foreign key
clubID
Number(3) Primary key, Foreign key
Fee
Number(4,2)

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!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Step 3

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 2 images

SEE SOLUTION Check out a sample Q&A here

Similar questions

Write SQL queries to create tables with keys and relations corresponding to each of thefollowing question
I need help with understanding how to do the SQL code for the following question (attached is the CSV database file): These are the header titles from the picture: ProductModelName, ProductCategory, ProductPrice, RetailerName, RetailerZip, RetailerCity, RetailerState, ProductOnSale, ManufacturerName, ManufacturerRebate, UserID, UserAge, UserGender, UserOccupation, ReviewRating, ReviewDate, Review Text 1. Get the total number of products reviewed and got Rating 5 in Every City
SQL Help
Task 1:Create a database Entity and Relationship Diagram (ERD) for the following application (Submitted An image of the Diagram By using Any Application:Application: The Application idea: It is a mobile application called (Khair) to help people. This assistance is moral, material. This assistance is also provided by good people in our community. Example to understand the Application idea more: For example, a person urgently needs a medical chair or bed and does not have the ability to purchase what he needs. The medical chair or bed is requested through the application, and whoever has what he requests initiates the request through the application’s messages and determines the meeting place. Application models (page): Login included: login registration login included: user name password New registration included: name E-mail Password phone number date National Identification Number Home page included: Request - applicant name - address - send message Messages list form included: list…
Using the Crow’s Foot Diagrram technique, draw a database design for the attached database.
Consider the following table in a relational database.Last Name Rank Room ShiftSmith Manager 234 MorningJones Custodian 33 AfternoonSmith Custodian 33 EveningDoe Clerical 222 MorningAccording to the data shown in the table, which of the following could be candidate keys of the table?a) {Last Name}b) {Room}c) {Shift}d) {Rank, Room}e) {Room, Shift}
QUESTION 1 SQL: Consider the following database schema for flight reservation database. A flight can be booked by many passengers, and a passenger can book different flights. For each booking, the date of the flight and price are recorded. The database schema is given below Flight (FID:int, Airline:string(unique), DepartingAirport:string, ArrivalAirport:string, Rating: int (1,2,3,4,5) ) Booking (FlightID: int, PassengerCPR: int, Date: date, Time: time, Price: double) Passenger (CPR:int, FirstName: String, LastName: String, Address:String) Write SQL Code to: Create the Flight Table.List the Departing Airport and Arrival Airport for all flights with rating 4 or 5.
PLZ help with the following: Using Oracle SQL Translate your ER diagram into a relational schema in the form of SQL DDLs. Choose appropriatedata types for each attribute and include primary key and foreign key constraints, Check and NotNull constraints.
please answer with proper explanation and step by step solution.
Consider the given schema for Library Database. Convert the chema into SQL.
how to build a bookstore database with Entities for Tables Book Authors Bookstore Members Sales transaction Inventory
Write the appropriate SQL DDL statements to declare the relational libraryDatabase. Diagram of Figure 4.6. Select the keys and reference actions that are triggered.