Introduction
根据提供的数据模型代写SQL语句文件，注意每个属性的类型和长度，以及约束即可。
The Student Conversation online newspaper
Your software startup has been hired to design and build a new online newspaper. The Student Conversation newspaper is modelled on TheConversation.com, a website featuring content written by academics. (However it is not identical to The Conversation – be careful to base your model on the requirements in this document.) The purpose of the Student Conversation is to provide a similar forum but with content written by university students. Though only students will write articles, any member of the public will be able to read and comment on them.
Your programmers will write the software for this new online newspaper. As the data modeller, your job is to design a database that can handle the system’s storage requirements. The required software and data storage features are detailed in this document.
Stories
The website displays a number of stories, with about ten new stories published each day. Each story is classified within one of seven sections (‘Local news’, ‘World news’, ‘Australia’, ‘Melbourne’, ‘Opinion’, ‘Education, ‘Technology’).
The content of each story consists of the main story text (maximum 10,000 characters long), as well as a headline (maximum 100 characters long), and a short “lede”, which is a sentence of 10 to 20 words that summarizes the story.
Each story contains one picture, positioned just under the headline at the top of the story. Pictures are stored in the database along with a caption (maximum 50 characters). We sometimes use the same picture in several stories – but we don’t want to waste space by storing it more than once.
A story is written by one or more of our authors, who submit the story to us on a particular date. If a story is considered worthy, it is edited by one of our editors and then published on a particular date. Editors choose, for each story, how many ads will be displayed with it – the number is typically between 1 and 4.
Staff
Any university student can be an author and submit stories to the Student Conversation. With ten new stories per day, there are likely to be hundreds or even thousands of authors after a few years. We expect that many authors will write more than one story over time. A small number of students work as editors. For conflict‐of‐interest reasons, it is not possible to be both an author and an editor ‐ once a person submits an article, they can never become an editor.
We call authors and editors “staff”, though they are current university students and are not paid. About each staff member we store their given and surnames, which university they attend and their student number, and when they join (and later leave) the newspaper. Authors but not editors also record a photo and short bio (max 200 characters of text).
Website
Readers will be able to click on one of the section headings, or on an author’s name, and see a list of all the relevant stories. We list out each story’s headline and lede: then if the reader clicks on a headline we display the entire story.
In addition we have a tagging system that allows authors and editors to attach tag‐words to a story. Readers can click on a tag and see listed all the stories tagged with that word. We store a list of tags which we can add to as required.
Readers
Our readers, if they wish, can choose to register themselves in our database, recording a username and password, email address and date of birth. Many of our readers do not register and thus their website activity is anonymous.
Readers who have registered can comment on stories. They can also click “Like” on a story or on a comment – and if they change their mind later, they can “Unlike”.
When we display a story we show the number of likes the story has received, and list out the comments below it (with all of their likes). With each comment we show the username of the person who commented and the time they commented. We restrict comments to 1024 characters. Editors can delete any comments considered unsuitable for display: when this occurs the comment is not actually deleted from the database, but simply marked as deleted, and we record which editor deleted it and when. If a reader hovers over the number of likes, we list out the first few usernames of the people who clicked Like – the reader can click to see the full list.
We maintain a social graph that allows readers to “follow” other readers. If reader A is following reader B, and reader B posts a comment, reader A will receive a notification with a link to reader B’s comment. “Following” is not reciprocal: if reader A follows reader B, it does not necessarily imply that reader B follows reader A. We needn’t store notifications in the database.
Advertising
Our advertising system funds the newspaper.
Whenever a reader clicks to view a story, we choose the appropriate number of advertisements from our collection and display them beside the story. Whenever this occurs we keep a record of which story and which ads were displayed, which reader viewed them (if their identity is known), and when.
Each ad consists of a headline of 25 characters, a text line of 35 characters, and a display URL of 35 characters. Each ad is placed into our system by one of our registered vendors. For each vendor we store a name and address.
To help place ads in appropriate stories, each ad is associated with one tag.
Vendors can also choose an age‐range for their ad: when known readers view a story, we only choose ads that match their age. The age ranges are kept in a separate table: currently they are 0‐15, 16‐20, 21‐30, 31‐45, 46+, but the database should allow these to be changed easily.