Relational Model Assignment Solution

$35.00

Description

Consider the UML class diagram shown below. Create the corresponding SQL that implements the equivalent relational model, fulfills the use cases, implements the relations, and enforces the constraints. A link to this assignment can be found here.

UML Class Diagram

UML class diagram Image missing

Create Schema (20pts.)

Use SQL to create the following schemas with the properties, data types, and relations as described in the UML class diagram. All tables should define a primary key called “id” configured to auto increment if no value is provided. Foreign keys should have the same name as the table they refer to. Enforce required fields and cardinality. Do not use “enum” to implement the <<enumeration>>. Implement associations between the tables either as primary key/foreign key and/or additional mapping tables, e.g., roles.

1. (0pts) Create a brand new schema called hw2_lastname_firstname_fall_2017, lastname and firstname are YOUR last and first names. Do all your work in that schema.

2. (5pts) Create tables person, developer, and user. Implement generalization using separate tables for each class. Name the constraint on the foreign keys using the following pattern: subclass_superclass_generalization, where subclass and superclass are the subclass and superclass in the diagram. For instance if person is a superclass and faculty is a subclass of person, then the foreign key constraint name would be faculty_person_generalization

3. (5pts) Create tables website, page, widget, heading, html, youtube, image. Implement generalization using a single table. Use a new field called type to discriminate for the type. Use the class name as the values of the field. Default heading size should be 2

4. (5pts) Create tables address and phones

5. (5pts) Create tables for website and page roles, and enumerations

Implement Inserts (20pts.)

Provide SQL queries that insert the data shown below. Note that some will require inserting into more than one table. Use the IDs where provided, otherwise the ID fields should be configured to auto increment. Auto generated IDs can be used where not specified. Nested queries may be used to inquire about previously inserted data. Later inserts can assume data exists from earlier inserts.

1. (4pts.) Create the following developers and users. Insert into the correct tables

depending on the type

id

Username

Password

First

Last

Type

Email

Key

12

alice

alice

Alice

Wonder

Developer

alice@wonder.com

4321rewq

23

bob

bob

Bob

Marley

Developer

bob@marley.com

5432trew

34

charlie

charlie

Charles

Garcia

Developer

chuch@garcia.com

6543ytre

45

dan

dan

Dan

Martin

User

dan@martin.com

7654fda

56

ed

ed

Ed

Karaz

User

ed@kar.com

5678dfgh

2. (4pts.) Create the following web sites for the developers above. For both the created field and updated field, use the date your assignment will be graded, e.g., do not hardcode it

id

Name

Description

Owner

Editor

Admin

Visits

123

Facebook

an online social media and social networking service

alice

bob

charlie

1234234

234

Twitter

an online news and social networking service

bob

charlie

alice

4321543

345

Wikipedia

a free online encyclopedia

charlie

alice

bob

3456654

456

CNN

an American basic cable and satellite television news channel

alice

bob

charlie

6543345

567

CNET

an American media website that publishes reviews, news, articles, blogs, podcasts and videos on technology and consumer electronics

bob

charlie

alice

5433455

678

Gizmodo

a design, technology, science and science fiction website that also writes articles on politics

charlie

alice

bob

4322345

3. (4pts.) Create the following pages for the web sites above. Use the semester’s start date

for the created field. Use the assignment’s due date for the updated field.

id

Name

Description

Website

Editor

Reviewer

Writer

Views

123

Home

Landing page

CNET

alice

bob

charlie

123434

234

About

Website description

Gizmodo

bob

charlie

alice

234545

345

Contact

Addresses, phones, and contact info

Wikipedia

charlie

alice

bob

345656

456

Preferences

Where users can configure their preferences

CNN

alice

bob

charlie

456776

567

Profile

Users can configure their personal information

CNET

bob

charlie

alice

567878

4. (4pts.) Create the following widgets for the pages shown.

id

Name

Type

Text

Order

Width/Height

Url

Page

123

head123

heading

Welcome

0

null

null

Home

234

post234

html

<p>Lorem</p>

0

null

null

About

345

head345

heading

Hi

1

null

null

Contact

456

intro456

html

<h1>Hi</h1>

2

null

null

Contact

567

image345

image

null

3

50×100

/img/567.png

Contact

678

video456

youtube

null

0

400×300

https://youtu.be/h6

7VX51QXiQ

Preferences

5. (4pts.) Create the following phones and addresses for the users or developers shown

Username

Phones

Addresses

alice

123-234-3456

234-345-4566

123 Adam St., Alton, 01234,

234 Birch St. Boston, 02345

bob

345-456-5677

345 Charles St., Chelms, 03455,

456 Down St., Dalton, 04566,

543 East St., Everett, 01112

charlie

321-432-5435

432-432-5433

543-543-6544

654 Frank St., Foulton, 04322

Implement Queries (20pts.)

Write SQL to implement the following queries. Assume the data inserted in the prior problem set. Nested loops may be used.

1. (5pts.) Retrieve developers

a. Retrieve all developers

b. Retrieve a developer with id equal to 34 (charlie)

c. Retrieve all developers who have a role in Twitter other than owner (charlie, alice)

d. Retrieve all developers who are page reviewers of pages with less than 300000 visits (charlie)

e. Retrieve the writer developer who added a heading widget to CNET’s home page

(charlie)

2. (5pts.) Retrieve websites

a. Retrieve the website with the least number of visits

b. Retrieve the name of a website whose id is 678 (Gizmodo)

c. Retrieve all websites with videos reviewed by bob (CNN)

d. Retrieve all websites where alice is an owner (Facebook, )

e. Retrieve all websites where charlie is an admin and get more than 6000000 visits

3. (5pts.) Retrieve pages

a. Retrieve the page with the most number of views b. Retrieve the title of a page whose id is 234

c. Retrieve all pages where alice is an editor (About)

d. Retrieve the total number of pageviews in CNET

e. Retrieve the average number of page views in the Web site Wikipedia

4. (5pts.) Retrieve widgets

a. Retrieve all widgets in CNET’s Home page b. Retrieve all youtube widgets in CNN

c. Retrieve all image widgets on pages reviewed by Alice d. Retrieve how many widgets are in Wikipedia

Implement Updates (20pts.)

1. (5pts.) Update developer – Update Charlie’s primary phone number to 333-444-5555

2. (5pts.) Update widget – Update the relative order of widget head345 on the page so that it’s new order is 3. Note that the other widget’s order needs to update as well

3. (5pts.) Update page – Append ‘CNET – ‘ to the beginning of all CNET’s page titles

4. (5pts.) Update roles – Swap Charlie’s and Bob’s role in CNET’s Home page

Implement Deletes (20pts.)

1. (5pst.) Delete developer – Delete Alice’s primary address

2. (5pst.) Delete widget – Remove the last widget in the Contact page. The last widget is the one with the highest value in the order field

3. (5pst.) Delete page – Remove the last updated page in Wikipedia

4. (5pst.) Delete website – Remove the CNET web site, as well as all related roles and privileges relating developers to the Website and Pages