Quote please
Creating a small author-article-publication database In this assignment, we’ll follow the database design process with a step-by-step example. Your client, Wellness Week is a small, specialized publisher that publishes articles on health and wellness issues. The owner would like to have a small database that would track authors and articles. Phase 1: Analysis During the analysis phase the database designer will ask questions of the various stakeholders to try to get as complete a view as possible of the business requirements. It is very important at this stage not to limit interviews and interactions with a select few users or a subset of users. The director’s needs of the database, for example, might be quite different from someone who interacts regularly with authors. The designer might ask questions like · How do you define an author? Can an individual be listed as an author before even submitting an article? · What types of information (data) would you like to know/capture about authors? · How do you define an article? Are there different types of articles? What other sorts of information/data might be interesting to know about articles? · Where are the articles published? Online, print, or both? Can a single article appear in more than one type of publication? Below are some of the findings from the analysis phase. If you have additional questions or need clarification, do not hesitate to ask your instructor. · Authors can sometimes simply express interest in submitting an article. So it would be good to have them in the database, even if they haven’t submitted anything. · Wellness Week would like to have basic contact information for all authors: name, address, phone number, email. · Wellness Week currently publishes a monthly newsletter only. They do not currently publish online. Each monthly newsletter has a specific theme (title). It is possible that an article might be reprinted in more than one edition of a newsletter. · For each article, they would like to capture the author name, date of publication and length (in words). · Just as an author can exist in the database without an article, in the planning stages of a publication, it would be helpful to have a publication without an article. · The articles can have only one author. Phase 2: Design Based on this information, we can start to think about the conceptual design and consider the following entities: · Author · Article · Publication Would publisher be an entity? The answer is no, but if you are unsure of why not, ask your instructor! Now we can begin to consider the relationship between the various entities. · An author can write many articles, or one, or none. · An article can have only one author. · Does an article need to have an author? We did not ask that question in our analysis phase, but it would be a good idea to ask. We’ll consider that there are no ‘anonymous’ articles, so each article must have one author (and only one). We can further take a first shot at the following attributes: · Author: first name, last name, address, city, state, zip, country, phone number, email address · Article: title, author, length · Publication: title, date of publication The Entity Relationship Diagram might look like this:author article publication There is ONE many-to-many relationship in the figure above. As explained in the Relationships Among Entities document, many-to-many relationships must be converted into one-to-many relationships so they can be implemented in a DBMS. An intersection entity between Article and Publication should be created. We’ll call it article_pub. author article article_pub publication The next step in the design phase is to develop the logical and physical design. Here we’ll add attributes and create relationships between the entities and specify primary keys. Database Name: wellness_week author Field Definition/Type authorId primary key, integer firstName text lastName text address text city text state text zip text telephone text email text article Field Definition/Type articleId primary key, integer title text authorId foreign key, integer length integer article_pub Field Definition/Type articleId joint primary key, foreign key, integer publicationId joint primary key, foreign key, integer publication Field Definition/Type publicationId Primary key, integer Title text publicationDate text Phase 3: (Initial) Implementation Part 1 of the assignment (7 points): During the tutorials you’ve taken in this course, you have seen databases created at various stages. You have not, however, seen a database created from beginning to end. Looking back at the tutorials and SQLite documentation, your task is to write the SQLite code to create the database described here. It should attend to all business specifications described in this document. The code must be in SQLite, the version of SQL you have been using in the Khan Academy tutorials. If you simply do a search for ‘SQL’ ‘create database’, you will just as likely find some other flavor of SQL such as MySQL or PostgreSQL. You can use this New SQL script page at Khan Academy to test your work: https://www.khanacademy.org/computer-programming/new/sql Additional instructions: · Use AUTOINCREMENT for Primary Keys in author, article and publication tables · Respect the precise naming conventions for the overall database, tables and attributes as given in Phase 2: Design Phases 4-6: Implementation, Testing and Maintenance One could argue that Testing should logically occur before Implementation, since for many ‘Implementation’ implies ‘roll out’ of the database. Whichever order you put them in, testing is essential BEFORE the database bears the full brunt of hundreds or thousands of concurrent connections and the demands of users. Before the system has been populated with data, it is still theoretically possible to change the underlying schema of the database. Not always easy, but possible. Once the database has been populated with data, this becomes much more difficult, even impossible in certain situations. Maintenance, including working on keeping up the efficiency and speed of the database in the wake of updates and deletes, is an on-going task. Backing up a database is obviously important and accepting that occasionally full or partial restores of a data base will be necessary. Part 2 of the assignment (5 points): When you have successfully constructed the necessary code to create the database in SQL, you should populate the database with sample data of your choice, again using SQLite code. Note that the data need not be ‘real’, but should be realistic. For example, 177 Arbor Hill Drive, Silver Spring, MD 20903 is not a real address, but is realistic. 137 Abcdefg Avenue, Qwerty, KY 12345 is not realistic. Your data should have a minimum of 5 authors, 5 articles and 2 publications. Otherwise follow any rules listed earlier in the Design phase. As with Part 1, you may use the New SQL script page at Khan Academy to test your work: https://www.khanacademy.org/computer-programming/new/sql Part 3 of the assignment (Maintenance) (3 points): 1. If Wellness Week decided that it would like to allow multiple authors for an article, would this require changing the (underlying) database schema? Why or why not? 2. If Wellness Week decided that it would like to establish an online presence, with some reprints and some new content (articles), would this require changing the (underlying) database schema? Why or why not?