Database Design for a Community Portal: Mini Project 2, Schemes and Mind Maps of Database Management Systems (DBMS)

Download Database Design for a Community Portal: Mini Project 2 and more Schemes and Mind Maps Database Management Systems (DBMS) in PDF only on Docsity!

• Mini Project -

5. Update their Profile after logging in. Following Functionality is part of Database Design & Will be enhanced as features as required in the Capstone Project, Depending on time availability.
6. Send Messages to Each Other on the Portal
7. Create Threads & Post Replies to a Thread
8. Post Job Opportunities in the Portal Administrator should be able to perform following functions in the portal
9. Administer user data.
10. Send bulk email inviting programmers to register on the community portal The portal consist of the following Key pages (For Reference)
11. Community Portal Home Page
12. Registration Page
13. Registration Confirmation Page
14. Update Profile Page
15. Search Users Page
16. List Search Results
17. Public Profile Page
18. Registration Confirmation Email
19. Login Page
20. Forget Password Page
21. Design the Forget Password Confirmation Page Following Functionality is part of Database Design and will not be part of development & Will be enhanced as features are required in the Capstone Project, Depending on time availability.
22. Send Messages
23. Read Messages
24. Post in Message Board
25. List Message Board
26. Read A Thread
27. Post Job Opportunities
28. List Job Opportunities & Responses The scope of this assignment The scope is to Implement the database in MySQL a. Create the tables based on design in Mini Project 1 b. Take note of Field Name, Field Type & Size c. Implement the database in MySQL and provide screen capture d. Create a user name in MySQL which can be used by the application, give required access privileges. (Provide screen capture of the user with access privileges)

User Table

Field Name Data Type Size Note User id int Auto increment and PK FirstName Varchar 8 Not null constraint Last Name varchar 8 Not null constraint Email Varchar 20 Unique constraint Telephone int Not null constraint Address varchar 25 Not null constraint Company Varchar 10 Not null constraint Country varchar 10 Default constraint

User telephone

Field Name Data Type Size Note User id int Auto increment and PK Telephone id int FK

Telephone

Field Name Data Type Size Note Telephone id int PK Telephone int Not null constraint

Jobs

Field Name Data Type Size Note Job id int Auto increment and PK Job title Varchar 10 Not null constraint Company Id int 3 Not null constraint Company Varchar 10 Not null constraint Country varchar 10 Default constraint

Applicant varchar 8 Not null constraint Company contact varchar 10 Not null constraint

Messages

Field Name Data Type Size Note User id int Auto increment and PK Sender Name Varchar 10 Not null constraint Sender Id Int Not null constraint Receiver name Varchar 10 Not null constraint Receiver Id varchar 3 Not null constraint Sent date Int Not null constraint Sent time Int Not null constraint Receiver Field Name Data Type Size Note Receiver id int Auto increment and PK Message Id int FK Receiver details Field Name Data Type Size Note Receiver id int Auto increment and PK Receiver name Varchar 10 Not null constraint Receive time time Not null constraint

Account

SQL Script for User Table Table structure of User SQL Script for Telephone Table Table structure of Telephone SQL Script for User Telephone Table

Table structure of Telephone SQL Script for User Jobs Table structure of Jobs SQL Script for Applicant Table structure of Applicant

Table structure of receiver details SQL Script for Account Table structure of Account SQL Script for Bulk email Table structure of Bulk email

SQL Script for Job applicant Table structure of job applicant

Web pages in the Community portal and queries S No Web Pages Queries

1. Community Portal Home Page

2 Registration Page INSERT INTO User (FirstName, LastName, Telephone, Email, Company, Address, Country) VALUES ('hobi', 'leeha', '0508778934', '[email protected]', 'hp', 'Vjay2 road, china', 'china'); 3 Registration Confirmation Page

4 Update Profile Page UPDATE User SET company = 'samsung', Country = 'Thailand' WHERE User_Id = 1; 5 Search Users Page SELECT User_ID, FirstName, LastName, Telephone, Email, Company, Address, Country FROM USER WHERE country='China'; 6 List Search Results Select count First_name, last_name, city, country, company FROM User WHERE First_name=’win’or last_name=’’ or city=’Yuna’ or country=’Thailand’ or company=’google’; 7 Public Profile Page SELECT User_ID, FirstName, LastName, Telephone, Email, Company, Address, Country FROM USER_Profile WHERE country='Sri Lanka'; 8 Registration Confirmation Email

9 Login Page SELECT email, password FROM USER WHERE email, password = ‘[email protected]’, ‘john123’;

10 Forget Password Page Insert into User (email) Values (‘[email protected]’) 11 Forget Password Confirmation Page Update account set password= ‘abc123’ Where user_Id=2;