Assume you are in charge of establishing the table structures for a sports news
ID: 3732542 • Letter: A
Question
Assume you are in charge of establishing the table structures for a sports news website. You’ve already identified the list of subjects and characteristics (Figure 1), have interviewed the manager and collected a sample for current website (Figure 2), as follows.
Interview:
You: As of now, your website shows a marquee which shows the headlines of the news. When the reader clicks on a headline, the system shows the story. What’s your vision for the new website? Any changes?
Participant: Yes, a lot of changes. We would like the website to allow readers to comment on news stories. For each comment, we would like to show the user name, date/time, and comment. This pretty much is like Facebook’s style of reader comments. Readers can comment on each other’s comments as well.
You: Sounds good. What other features would you like to have?
Participant: We would like to keep track of the views for each story. Each view should have a date/time and IP address/user name associated with it. Further, this is a sports website for different kinds of sports (e.g. basketball, baseball). We want to categorize our news stories based on the sport type.
Figure 1: List of Subjects and List of Characteristics
Figure 2: Sample showing the details of a news story.
Please answer the following questions. You should explain how you’ve refined your preliminary table list using the techniques we learned in class. Make sure you choose good names for your tables and fields as well.
1- Identify Preliminary List of Fields
2- Construct Preliminary Table List
3- Construct Final list of Tables (with type and description for each table).
4- Construct the Table structures (with fields assigned to each table).
You: As of now, your website shows a marquee which shows the headlines of the news. When the reader clicks on a headline, the system shows the story. What’s your vision for the new website? Any changes?
Participant: Yes, a lot of changes. We would like the website to allow readers to comment on news stories. For each comment, we would like to show the user name, date/time, and comment. This pretty much is like Facebook’s style of reader comments. Readers can comment on each other’s comments as well.
You: Sounds good. What other features would you like to have?
Participant: We would like to keep track of the views for each story. Each view should have a date/time and IP address/user name associated with it. Further, this is a sports website for different kinds of sports (e.g. basketball, baseball). We want to categorize our news stories based on the sport type.
List of Characteristics Story Title Story Description Reader Name View Date Time View IP Address List of Subjects Story Reader ViewExplanation / Answer
List of Characteristics
Story Title
Story Description
Reader Name
View Date Time
View IP address
List of Subjects
Story
Reader
View
Answer 1:
From the list of characteristics we the following are the list of fields which are needed to construct the website. This is based on the interview and list of subjects and charactaristics.
story id , Story title, story description, comments, username, Timestamp, view count, view timestamp, view Ip address, story category, story subjects.
Answer 2:
Based on the fields we can find the preliminary list of tables as follows:
Story, Story subject, Story Category, Story Comments, Story Views
We could able to find the preliminary list of tables by checking, which are the unique entities in the requirement which can be constructed as a table. In addition, the entities which require to be stored in the database as tables and has fields associated with it.
Answer 3:
Final list of tables are as follows:
Story Table: List of stories that will be displayed on the website.
Story Subject: List of stories listed by is subject type. One story can have multiple subject type.
Story Category: List of stories listed by is Category type. One story can be of multiple Category type.
Story Comments Table: List of comments received for each story.One story may have multiple comments.
Story Views: List of views received for each story. One story can have multiple views.
Answer 4:
The final table structures are as follows:
STORY (story_id , Story_title, story_description)
STORY CATEGORY (story_id, story_categories)
STORY SUBJECT (story_id, story_subjects)
STORY COMMENTS (story_id, story_comments,comment_timestamp)
STORY VIEWS (story_id, view_timestamp, view_Ipaddress)
I have answered your question. Please do not forget to give a positive feedback to the answer. Thank you.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.