[SOLVED] JDBC Assignment CS5200 - Introduction to Relational Databases

Consider the UML class diagram shown below. Create the corresponding Java Data Model that implements the equivalent relational model, fulfills the use cases, implements the relations, and enforces the constraints. Implement Data Access Objects (DAOs) that encapsulate access to the database applying best practices discussed in class. ​A link to this assignment can be found here​.

UML Class Diagram

Disregard the attributes for the User class in this diagram. See the below diagram for updated user attributes.

 

 

Create Java Data Model (5pts.)

Use Java to implement the data model. Make use of best practices discussed in class. Each instance variables must be declared as private and have public setters and getters.

 

1.  Person.java 2.  User.java 3.  Developer.java 4.  Website.java 5.  Page.java 6.  Widget.java

7.   HeadingWidget.java 8.   HtmlWidget.java 9.   ImageWidget.java 10.  YouTubeWidget.java 11.  Role.java 12.  Priviledge.java

Constructor Requirements: (5Pts)

There are no constructor requirements for classes not specified below. You are free to implement constructors as you see fit.

1. Developers.java

1. Should create an instance of developer when developer Key, Developer ID, Developer First Name, Developer Last Name, with rest of the fields with default values specified by you.
2. Should also create an instance of developer when developer key, developer ID, developer first name, developer last name, developer user-name, developer password, email and dob are passed as parameters.

2. User.java

1. Should create an instance of user when user ID, First Name, Last Name. All new users cannot be approved users.

3. Websites.java

1. Should create an instance of website when id, website name, description, created, updated, and views are passed as parameters.

4. Page.java

1. Should create an instance of Page when id, title, description, created, updated and views are passed as parameters.

5. Widget.java

1. Should create an instance of Widget when ID, name, width, height, cssStyle, cssCLass, text, order are passed as parameters.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

UML Diagram

The UML diagram defines several associations between various classes using lines and symbols. These are implemented in SQL using primary and foreign key fields. In Java you’ll also capture the primary key fields id, but instead of the foreign key fields, you’ll capture some collection of instances of the child class. As an example, consider the relation between entities Website and Page illustrated below. The UML class diagram captures the association using a line and the cardinality of how many of each participate in relation. The diagram says that each Website instance has zero or more Page instance.

 

UML

SQL

Java

CREATE TABLE Website (      class Website {

id int,                     int id;

PRIMARY KEY (id)            Collection<Page> pages;

);                            addPage(Page) { }

CREATE TABLE Page (           removePage(Page) { }

id int,                   }

websiteId int,            class Page {

FOREIGN KEY (websiteId)   Website website;

REFERENCES Website(id) }

);

 

 

SQL implements the relation using primary and foreign keys. In the example, the Page​ table declares foreign key ​websiteId which references to the ​id field in the ​Website table. If we consider the relation between websites and pages as a whole/parts relation, where pages are the various parts that make a whole Website, then the relation implementation states that each part (page) know who the whole is (website). The Java implementation captures the relation between websites and pages by declaring a collection of pages in the ​Website class. That is, each ​Website instance has a complete list of references to its Page​ instance. Notice the difference in the direction of the relation between the SQL and the Java relation implementation. Whereas in the SQL implementation the relation is from the part to the whole, e.g., page records have foreign key references to websites, in the Java implementation, the relation goes from the whole to the parts, e.g., the websites have a collection of references to page instances. Additionally, the Java implementation also can declare a reference from the page to the website which matches the SQL implementation, but this is less common in object oriented solutions.

 

A good naming convention for primary and foreign keys is to name all foreign keys “id​ “​ (or “ID​ ​”) and name the foreign keys that refer to them as the table name followed by “Id​ “.​ For instance, from the example above, the foreign key from table ​Page to table ​Website should be named websiteId​ and should refer to the primary key field ​id​ in table ​Website​.

Naming Conventions (5pts)

These are the naming conventions for the project and they must be followed. Else your JUnit grader will fail and would not grade your assignments.

 

1. Your Projects must be named cs5200_fall2018_lastname_firstname_jdbc
2. All Packages must be created in src->main->java->edu->northeastern->cs5200
3. Inside your edu.northeastern.cs5200 package,
   1. All DAOs must be in package called ​
   2. All models must be in package called models​ ​.
   3. If other packages are created, please specify their names in README.txt.

Implementation(5Pts)

**All applications should be deployed in AWS, unless otherwise excused.**

 

In src->main->java>edu>northeastern>cs5200 package, create a public Singleton class called Connection.java. 

 

public class Connection {

 

private static final String DRIVER = “com.mysql.jdbc.Driver”;

private static final String URL = “jdbc:mysql://<AWS ENDPOINT>/<SCHEMA NAME>”; private static final String USER = <AWS RDS USERNAME>; private static final String PASSWORD = <AWS RDS PASSWORD>;

 

public static Connection getConnection() throws ClassNotFoundException,

SQLException {

Class.forName(DRIVER);

return DriverManager.getConnection(URL, USER, PASSWORD);

}

 

public static void closeConnection(Connection conn) {

try {      conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block             e.printStackTrace();

}

}

}

 

So, everytime the test method calls the static method called getInstance() of the

Connection.java class, it should return the same instance of the connection rather than creating a new instance.

The class should have method called closeConnection(). It must be a static method and should not return anything. It closes the above said connection.

Create Data Access Objects (40pts.)

A good practice is to encapsulate all data access in a set of classes whose only responsibility is to provide an API to the database. These types of classes are commonly referred to as Data Access Objects, or DAOs. Implement the following DAOs that encapsulate the access to the database for each of the entities and relations in the UML class diagram.  

1. DeveloperDao.java 4. WidgetDao.java 2. WebsiteDao.java 5. RoleDao.java 3. PageDao.java   6. PriviledgeDao.java

Implement the Developer Data Access Object

In a file called ​DeveloperImpl.java​, create an interface shown below. Please do not make any modification to the interface.

 

1. void createDeveloper(Developer developer)

inserts properties in ​developer​ instance parameter in tables ​Developer​ and Person​

2. Collection<Developer> findAllDevelopers() returns all joined records from ​Developer and ​Person tables as a ​Collection of Developer​ instances.

3. Developer findDeveloperById(int developerId)

returns a joined record from ​Developer and ​Person tables whose ​id field is equal to the developerId​ parameter

4. Developer findDeveloperByUsername(String username) returns a joined record from ​Developer and ​Person tables whose ​username field matches the parameter.

5. Developer findDeveloperByCredentials(String username, String password) returns a joined record from ​Developer and ​Person tables whose ​username and password fields match the parameters​

6. int updateDeveloper(int developerId, Developer developer) updates records in ​Developer and ​Person tables whose id​ field is equal to developerId New record field values are set to the values in the ​developer instance parameter.
7. int deleteDeveloper(int developerId) deletes records from ​Developer and ​Person tables whose ​id field is equal to developerId ​ ​Do not make any modifications to the instance provided.

 

In a file called DeveloperDao.java​ ,​ develop a class which implements the Developer interface and encapsulates all database interaction between the ​Developer and ​Person entities and relations.

 

When returning the developer instance, create a new developer instance and do not modify the existing developer instance provided in the parameters.

Implement Website Data Access Object

In a file called ​WebsiteImpl.java​, create an interface as shown below. Do not make any modifications to the interface.

 

1. void createWebsiteForDeveloper(int developerId, Website website)

inserts properties in ​website instance parameter into the ​Website table. The website’s developerId foreign key refer to ​Developer ​table primary key ​id whose value is equal to the developerId​ parameter. You can use the owner’s user id as the foreign key​

2. Collection<Website> findAllWebsites()

returns all records from ​Website​ table as a ​Collection​ of ​Website​ instances

3. Collection<Website> findWebsitesForDeveloper(int developerId) returns all records from ​Website table as a ​Collection of ​Website instances whose developerId​ is equal to the ​developerId​ parameter Website findWebsiteById(int websiteId)

returns a record from ​Website​ table whose ​id​ field is equal to the ​websiteId​ parameter

5. int updateWebsite(int websiteId, Website website)

updates record in ​Website table whose ​id field is equal to ​websiteId parameter. New record field values are set to the values in the ​website​ instance parameter

6. int deleteWebsite(int websiteId)

deletes record from ​Website​ table whose ​id​ field is equal to ​websiteId parameter​

 

In a file called ​WebsiteDao.java​, implement a class that encapsulates all database interaction between the ​Website​ and ​Developer​ entities and relations.

 

When returning the website instance, create a new website instance and do not modify the existing developer instance provided in the parameters.

Implement Page Data Access Object

In a file called ​PageImpl.java, ​create an interface with the methods given below. Do not make any modifications.

 

1. void createPageForWebsite(int websiteId, Page page)

inserts properties in page​    instance parameter into the Page​      table. The page’s websiteId​            foreign key refer to Website​       ​table primary key id​ whose value is equal to the ​websiteId parameter

2. Collection<Page> findAllPages()

returns all records from ​Page​ table as a ​Collection​ of ​Page​ instances

3. Page findPageById(int pageId)

returns a record from ​Page​ table whose ​id​ field is equal to the ​pageId​ parameter

4. Collection<Page> findPagesForWebsite(int websiteId)

returns all records from ​Page table as a ​Collection of ​Page instances whose ​websiteId is equal to the ​websiteId​ parameter

5. int updatePage(int pageId, Page page)

updates record in ​Page table whose ​id field is equal to ​pageId parameter. New record field values are set to the values in the ​page​ instance parameter

6. int deletePage(int pageId)

deletes record from ​Page​ table whose ​id​ field is equal to ​pageId​ parameter

 

In a file called ​PageDao.java​, implement a class that encapsulates all database interaction between the ​Website​ and ​Page​ entities and relations. Use the interface shown below.

 

When returning the page instance, create a new page instance and do not modify the existing developer instance provided in the parameters.

Implement Widget Data Access Object

In a file called ​WidgetImpl.java, ​create an interface with the methods given below. Do not make any modifications.

 

1. int createWidgetForPage(int pageId, Widget widget)

inserts properties in ​widget instance parameter into the ​Widget table. The widget’s pageId foreign key refer to ​Page ​table primary key ​id whose value is equal to the pageId​ parameter

2. Collection<Widget> findAllWidgets()

returns all records from ​Widget​ table as a ​Collection​ of ​Widget instances​

3. Widget findWidgetById(int widgetId)

returns a record from ​Widget​ table whose ​id​ field is equal to the ​widgetId​ parameter

4. Collection<Widget> findWidgetsForPage(int pageId) returns all records from ​Widget table as a ​Collection of ​Widget instances whose pageId​ is equal to the ​pageId​ parameter
5. int updateWidget(int widgetId, Widget widget) updates record in ​Widget table whose ​id field is equal to ​widgetId New record field values are set to the values in the ​widget​ instance parameter

6. int deleteWidget(int widgetId)

deletes record from ​Widget​ table whose ​id​ field is equal to ​widgetId​ parameter

 

In a file called ​WidgetDao.java​, implement a class that encapsulates all database interaction between the ​Widget and ​ ​Page entities and relations.​

 

When returning the widget instance, create a new widget instance and do not modify the existing developer instance provided in the parameters.

Implement Role Data Access Object

In a file called ​RoleDao.java​, implement a class that encapsulates all database access that manage website and page roles assigned to ​Developer​. Use the interface shown below. Feel free to implement additional or improved versions of the API

 

1. assignWebsiteRole(int developerId, int websiteId, int roleId)

inserts into table Role a record that assigns a developer whose id is developerId, the role with roleId, to the website with websiteId

2. assignPageRole(int developerId, int pageId, int roleId)

inserts into table Role a record that assigns a developer whose id is developerId, the role with roleId, to the page with pageId

3. deleteWebsiteRole(int developerId, int websiteId, int roleId)

deletes from table Role a record that removes roleId from developerId, on websiteId

4. deletePageRole(int developerId, int pageId, int roleId)

deletes from table Role a record that removes roleId from developerId, on pageId

Implement Priviledge Data Access Object

In a file called PriviledgeDao.java​ ,​ implement a class that encapsulates all database access that manage website and page priviledges assigned to ​Developer​. Use the interface shown below. Feel free to implement additional or improved versions of the API

 

1. assignWebsitePriviledge(int developerId, int websiteId, String priviledge)

inserts into table Priviledge a record that assigns a developer whose id is developerId, the priviledge with priviledgeId, to the website with websiteId

2. assignPagePriviledge(int developerId, int pageId, String priviledge)

inserts into table Priviledge a record that assigns a developer whose id is developerId, the priviledge with priviledgeId, to the page with pageId

3. deleteWebsitePriviledge(int developerId, int websiteId, String priviledge)

deletes from table Priviledge a record that removes priviledgeId from developerId, on websiteId

4. deletePagePriviledge(int developerId, int pageId, String priviledge)

deletes from table priviledge a record that removes priviledgeId from developerId, on pageId

 

Exercise Your Data Model and DAOs (20pts.)

In a file called hw_jdbc_last_first.java, use the Data Model and Data Access Objects implemented earlier, to create the data shown below

 

1. (5pts.) 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	[email protected]	4321rewq
23	bob	bob	Bob	Marley	Developer	[email protected]	5432trew
34	charlie	charlie	Charles	Garcia	Developer	[email protected]	6543ytre
45	dan	dan	Dan	Martin	Use	[email protected]	7654fda
56	ed	ed	Ed	Karaz	User	[email protected]	5678dfgh

 

2. (5pts.) 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	charlie	alice	bob	4322345
		writes articles on politics

 

3. (5pts.) 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	12343 4
234	About	Website description	Gizmodo	bob	charlie	alice	23454 5
345	Contact	Addresses, phones, and contact info	Wikipedia	charlie	alice	bob	34565 6
456	Preferences	Where users can configure their preferences	CNN	alice	bob	charlie	45677 6
567	Profile	Users can configure their personal information	CNET	bob	charlie	alice	56787 8

 

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

 

Name	Type	Text	Order	Width/Height	Url	Page
head123	heading	Welcome	0	null	null	Home
post234	html	<p>Lorem</p>	0	null	null	About
head345	heading	Hi	1	null	null	Contact
intro456	html	<h1>Hi</h1>	2	null	null	Contact
image345	image	null	3	50×100	/img/567.png	Contact
video456	youtube	null	0	400×300	https://youtu.be/ h67VX51QXiQ	Preference s

 

Implement Updates (15pts.)

Using the DAOs implemented earlier, do the following updates

1. Update developer – Update Charlie’s primary phone number to 333-444-5555
2. 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. Update page – Append ‘CNET – ‘ to the beginning of all CNET’s page titles
4. Update roles – Swap Charlie’s and Bob’s role in CNET’s Home page

Implement Deletes (15pts.)

Using the DAOs implemented earlier, do the following deletes

1. Delete developer – Delete Alice’s primary address
2. 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. Delete page – Remove the last updated page in Wikipedia
4. Delete website – Remove the CNET web site, as well as all related roles and privileges relating developers to the Website and Pages

Stored Procedures (10pts.)

Using the DAOs implemented, create stored procedures for the following cases.

1. Create a Stored Procedure “getUnasnweredQuestions” which retrieves the question with most number of answers and no correct answer. Group the questions by module and print the question text and numbers of answer for the question. (5M)
2. Create a Stored Procedure, “endorsedUsersforWeek”, which retrieves the 5 most endorsed users (having the most number of correct answers for the questions posted in the given week). If there multiple endorsed Users per week, sort by their First Name. (5M)

Deliverables

In the folder provided to you, submit your entire code into the directory on Github. And also make a submission on Blackboard with the submission text as your Github URL.