1. Convert the ER diagram below into a set of tables and schema's in SQL code. and Create the following into tables. Except for one case you should have a total of 15 tables. The exception is both observation and it's Associate enity Taken are one table
The ER Model is focussed to be a description of real-world entities. Although its development/construction is done in such a way as to allow fair and easy translation to the relational schema model, this is not fully trivial process. The ER diagram represents the conceptual level of database design while the relational schema represents the logical level for the database design. Following simple rules are followed:
ADDRESS( cityAddress, city, state ) candidate key:cityAddress, city , state
PERSON( pid,name ) candidate key: pid
OBSERVATION (oid, date, temperature, humidity, precip. ,city), candidate key: oid foreign key: city
OBSERVER ( pid ), candidate key: pid
METEREOLOGIST( pid ), candidate key: pid
CONSUMER( pid ), candidate key: pid
WARNINGS( wwid,description,date,city) candidate key: wwid
FORECAST( fid,city,date,low,high) candidate key: fid
WEATHERPROGRAM( wid,name ) candidatekey; wid
LIVESAT(city, streerAddress, state,pid)
RECORDS(oid,pid,method)
ISSUES(wwid.pid)
PRESENTS(fid,pid)
VIEWS(pid,fid)
MAKES(wid,fid,oid)
15 tables will be created in all along with relationship tables
and only 9 tables if relationships are shown as foreign keys
CREATE TABLE IF NOT EXISTS Person ( pid int(6) unsigned AUTO_INCREMENT, name varchar(), PRIMARY KEY (pid) );
CREATE TABLE IF NOT EXISTS Address ( state varchar(10), streetAddress varchar(20), city varchar(20) unsigned AUTO_INCREMENT, PRIMARY KEY (city), );
CREATE TABLE IF NOT EXISTS Observation ( oid int(5) unsigned AUTO_INCREMENT, date int(10), temperature int(5), humidity int(5), precipitation int(5), PRIMARY KEY (oid), FOREIGN KEY(city) REFERENCES ADDRESS( city) );
CREATE TABLE IF NOT EXISTS Metereologist ( pid int(4) unsigned AUTO_INCREMENT, PRIMARY KEY (pid), );
CREATE TABLE IF NOT EXISTS consumer( pid int(4) unsigned AUTO_INCREMENT, PRIMARY KEY (pid) );
CREATE TABLE IF NOT EXISTS Observer( pid int(4) unsigned AUTO_INCREMENT, PRIMARY KEY (pid) );
CREATE TABLE IF NOT EXISTS Forecast ( fid int(4) unsigned AUTO_INCREMENT, city varchar(10), date int(10), low int(5), high int(5), PRIMARY KEY (fid), FOREIGN KEY (fid) REFERENCES metereologist(pid),
FOREIGN KEY (fid) REFERENCES consumer(pid),
FOREIGN KEY (fid) REFERENCES observer(pid),
);
CREATE TABLE IF NOT EXISTS weather program( wid int(4) unsigned AUTO_INCREMENT, name varchar(10), PRIMARY KEY (wid), FOREIGN KEY( wid) REFERENCES FORECAST (fid), );
1. Convert the ER diagram below into a set of tables and schema's in SQL code....
Questions: Assume you are creating a database for IS paint store. Suppose you have an ER diagram like the following: Please answer the following questions. Explain your answer. 1. Convert the ER diagrams to relational tables. 2. Specify primary keys and foreign keys in each table. For foreign keys, specify which primary key it references to. 3. Please create the tables in SQL for the relational tables in our ER diagrams. You also need to define primary keys and foreign...
Put all of your SQL code in a file named grades.sql and submit it below. Download the starter code, which contains import_grades.sql and the grades.csv file. Using he import_grades, sql file, create your database and table. - 0 eded. 1 T Une Modify the LOAD DATA INFILE to correct the path to load the grades.csv file, and add/remove LOCAL he only modification you may make to the import_grades.sql or the grades.csv files. The data represents grades for assignments in a...
need help
Create or edit the SQL statements required to accomplish the following tasks. All columns should have a column name, feel free to create appropriate aliases Use screen shots to ca evidence that each task is completed; include the SQL records before and after statements are executed, as appropriate. Include all work, even if you get errors. Paste your screen shots into a single PowerPoint file pture statements output from the statements, and views of the object explorer when...
Question 1.Write a SQL statement for each of the following
questions based on the above tables (50 Points).
1) Create “Enrollment” table.
2) Change the credits of “Database” course from 2 to 3.
3) List the course titles (not Course_No) and grades John Doe
had taken.
4) Calculate average of Café Balance, and name new field as
Average Balance.
5) Create a roster for “Database” course (list student ID, last
name, first name, major, and status for each student enrolled...
Instructions: You are required to create ERD and Enhanced ERD diagrams. And also translate your own created ERD & EERD diagrams in to Relational Model. Q# 1: Draw ER diagram based on the following database requirements. A Tutorial class may have one or more students. A student may be in one or more classes. Attributes of CLASS includes CLASS CODE, ROOM NAME and CLASS SIZE. Attributes of STUDENT include STUDENT ID, NAME and NATIONALITY. Translate your ER diagram in to...
CIS 411w spring 2017 Problem Set 11 1 Log in to your Oracle ApEx account. 2. Create a new table called email with this command: CREATE table email as (SELECT empno, ename||'.'||SUBSTR(JOB,1,2)||'@apex.com' as "EMAIL" from emp); Click à Run to create this table 3. Write a SQL query that JOINS the three tables emp,dept and email. This SQL query will return the empno, ename, job, sal, loc and email for each employee. Use the newer ANSI JOIN syntax rather...
these are all tables please see the tables and
questions are mentioned below
please see that all and I need answers asap
please write proper answer it's an easy task and don't take much
time please do it fast
thanks in advance
EMPLOYEE Remark Column Name EmployeeNumberINT Primary Key Yes No CHAR (25 CHAR (35 CHAR 25 NUMERIC INT CHAR (12 CHAR Name in the DEPARTMENT table Position No Number in the EMPLOYEE table Su OfficePhone EmailAddress No No No...
Describe briefly a database application case of your choice and represent your data in terms of 3 linked (via foreign key/s) tables. List their respective primary key and foreign key/s if any. Your database application case should be different from those already covered in the lectures or practicals. For the database case in the Additional Exercises of the previous practical, complete the proper construction of all the 5 tables in SQL, building all the pertinent primary and foreign keys there....
ONLY ODD NUMBERS. YOU MUST USE ORACLE OR MY SQL.
THANKS
Chapter 3 TAL Distributors Use SQL to complete the following exercises 1. Create a table named SALES_ REP. The table has the same structure as the REP table shown in Figure 3-11 except the LAST_NAME column should use the VARCHAR data type and the COMMISSION and RATE columns should use the NUMBER data type. Execute the command to describe the layout and characteristics of the SALES_REP table. Add the...
question One: Design an ER diagram for the following Dental Clinic database. Your diagram should have all the needed details. You may make any reasonable assumptions but you have to state them clearly. ------ The clinic has several dentists. Each dentist has a unique Number, name, nationality, multiple room-number, salary, birth-date and home-address (Box, City, Zip). Each client (patient) has a unique Code, name, home-phone, work-phone, address, and birth-date. Each client is assigned to one dentist. All future visits will...