Help with this question please: Need Help doing the following Translate your ER

Question

Help with this question please:

Need Help doing the following

Translate your ER diagram into a relational schema, and write the SQL statements to create the relations, capturing as many constraints (including key and foreign key constraints) as possible. Explain the constraints that you have captured, and if there are constraints that you could not capture, then explain why they could not be captured.

Uses Weapons Color Name Ivpe Name Alias Characters Holds Political Unit Gender Type Type Belongs Planet Associates Participate Name Location Mentor Mentorship Uses Battle Name Year Start Year Final Year Approved Vehicles Name Type

Explanation / Answer

Relational Schema

Characters(Name,Alias,Gender,Type)

Weapons(Type,Color)

Mentorship(Mentor,Apprentice,StartYear,FinalYear,Approved)

Vehicles(Name,Type)

Planet(Name,Location)

PoliticalUnit(Name,Type)

Battle(Name,Year)

underlined are primary keys

Table Creation in SQL

create table Characters

( Name varchar(50) NOT NULL,

Alias varchar(50),

Gender varchar(10),

Type varchar(20),

Primary Key(Name));

Create table Weapons

( Type varchar(30) NOT NULL,

Color varchar(15),

Primary Key(Type));

Create table Mentorship

( Mentor varchar(30) NOT NULL,

Apprentice varchar(30) NOT NULL,

StartYear int,

FinalYear int,

Approved char(5),

Primary Key(Mentor,Apprentice)

Foreign Key(Mentor) References Characters(Name),

Foreign Key(Apprentice) References Characters(Name));

Create table Vehicles

( Name varchar(20) NOT NULL,

Type varchar(20),

Primary Key(Name));

Create table Planet

( Name varchar(10) NOT NULL,

Location varchar(20),

Primary Key(Name));

Create table PoliticalUnit

( Name varchar(50) NOT NULL,

Type varchar(30),

Primary Key(Name));

Create table Battle

( Name varchar(50) NOT NULL,

Year int,

Primary Key(Name));

Constraints : Primary Key is the unique identifier in the table which is NOT NULL. Foreign Key constraint is used to connect two relations .

Foreign key in Mentorship table is captured by Mentor and Apprentice attributes .

In other tables Foreign keys are not captured .

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.