Creating a Book Publishing Database - Assignment1Data.xlsx

Guidelines for Database Creation

Questions 1. Create a database to keep track of books, authors, publishers, and the employees that work with each publisher. The database also keeps information about the jobs that employees can do at a publishing company. An Excel spreadsheet Assignment1Data.xlsx containing the data to be stored at creation (initial load) is provided. There are six tables: Authors, BookAuthor, Books, Employees, Publishers, and Jobs. Each table must have a primary key, relationships between tables must be defined, and relationships must enforce referential integrity. The following guidelines are given about the tables. a. Authors are in Canada and each one has an Id, first name, last name, phone, postal code, province, sex, salary, birth date and a book subject. The Id is the primary key. b. For each book, we keep track of the title Id, the title of the book, publisher, price, advance for the author, year-to-date sales (quantity sold to date), and the publication date. The primary key is the titleId. c. Several authors can publish a book. Information about authors and the book in which they were an author stored in the BookAuthors table. The database also keepstrack of the ordering of author names and how often each author is paid a royalty. The primary key for this table is composite, comprising of the author Id and the title Id of the book authored. d. For each publisher, we keep track of the publisher Id (which consists of 4 digits), name, city, state, and country. The publisher Id is the primary key. e. Employees work with specific publishers. The database keeps track of employee first name, middle initial, last name, job Id, job level, publisher with which they work and their hire date. Several employees can work for the same publisher and may have the same job id. Employees are identified by their employee Id (empId). f. The jobs that employees do are stored in the Jobs table. The database keeps track of the job Id, description, min job level required for this position. A job is identified by the Job Id. 2. Choose datatypes and properties that are most appropriate for each field. For instance, names must be text, dates must be date/time and so on. Also note the following: a. Make all fields required, except for employee middle initial and publisher state (since some countries do not have states). b. Include a validation rule and text for book prices: Price must be greater than $0. c. Assume that the following fields only allow data having the same structure/format as the given initial data. Add an appropriate Format and/or Input mask for the following fields. Also set any other applicable property for the fields and any other field based on the given data. i. titleId of books ii. publisherId iii. Author phone iv. Author postal code v. Books publication date d. Include a lookup for sex field, so that the domain is limited to F, M, or U. 3. Create the following relationships. A lookup must be included for each foreign key. a. A book may have many authors / books have an author b. An author may have several books / books may have several authors c. A book has a publisher / a publisher may be the publisher for many books d. An employee works with a publisher / a publisher may have many employees e. An employee has a job / a job may be done by many employees 4. Add some data manually as follows: a. Make yourself the Chief Executive Officer of a new publishing firm that is based in Winnipeg, Canada. You may make up any required data not specified, as necessary. b. Add a new book with two authors. Make yourself the first author. The book is published by the publishing firm at which you are the CEO. You may make up a name for the second author and other necessary data that is not provided. 5. Design the queries to do the following: a. List all the employees (first name, last name, job level and hire date) working with the publisher called Algodata InfoSystems. The list must be in order ascending order of job level. If two employees have the same job level, they must be listed in ascending order by their last name. The order of the columns must be as specified above. b. List all books (title, publisher, publication date, number of authors) with 2 or more authors. c. List all books (title, publisher, publication date) published in 2013 where the total year to date sales from the book has exceeded $5000. d. List the authors (Id, first name, last name) that published a book before their 21 st birthday. e. List the id, first name and title of books by all authors whose subject is ‘Action’. List must be in ascending order by id and then by book title if an author has more than 1 book. The columns must be in the order specified above. f. Accept a job level and display a list of jobs (with all details) that the job level can qualify for given the minimum level required for each job. Columns of your query result must be shown in the display order specified and must also satisfy any sort order specified.