Reading: week2 supplemental reading This HW has you apply that knowledge in this week's assignment by having you think about the following problem that can be solved with database tables:
Imagine that you have to design a database that would match information about job postings and internship opportunities for candidate NYU graduating students (So, instead of matching patrons to books as in the reading, you are matching students to jobs for this HW)
Assume that you currently have separate sets of information (data) about:
-Typical student information such as their identities, their grades, their courses taken, and other information, etc.
-Typical information about job postings and/or internships, such as role, requirements, location, paid/unpaid, and other characteristics, etc.
1) You would like to bring all of this information together in a relational database system. Consider what data elements or variables do you think are necessary?
2) How would you structure distinct tables? (i.e. what would constitute a row in your table(s)?)
3) How would you ensure information is stored efficiently?
4) What keys would you use to relate tables to each other? (i.e. Describe how information in one table would link with information in another table)
5) If you wanted to add the capability of 'automatically' matching students to jobs and vice versa, explain via an illustrative example, how this automatic matching might work for your design.
Just as the reading illustrated sample tables, and their relationships, your HW submission should show sample tables and relationships — so that I can understand your design, and how your design would work to solve the task at hand.
Relational Database Concepts for Beginners (source: adapted from CS101, D.Whisnant, Wofford College) A database contains one or more tables of information. The rows in a table are called records and the columns in a table are called fields or attributes. A database that contains only one table is called a flat database. A database that contains two or more related tables is called a relational database. There are other more complex kinds of databases, but this paper is going to focus on the what and why of relational databases. Here’s an easy way to understand the benefits of dividing your data into multiple tables: Imagine that you are responsible for keeping track of all the books being checked out of a library. You could use a single table (a flat database) to track all the critical information:
This table meets the basic need to keep track of who has checked out which book, but does have some serious flaws in terms of efficiency, space required, and maintenance time. For example, as voracious reader Bob checks out more books over time, you will have to re-enter all of his contact information for every book.
To re-enter Bob’s contact information wastes time, and increases the opportunity for error. Moreover, when an update is necessary (e.g. Bob’s phone number changes), each of Bob’s records must be located and corrected. If one of Bob’s records has a different phone number from the rest, is it a correction, a record overlooked during the last update, or a data-entry mistake?
These problems can be decreased by normalizing our data – in other words, dividing the information into multiple tables with the goal of having “a place for everything, and everything in its place.” Each piece of information should appear just once, simplifying data maintenance and decreasing the storage space required.
Now that the data are arranged efficiently, we need a way to show which records in the PATRONS table correspond to which records in the CHECKOUT table – in other words, who checked out which book. Instead of repeating everything we know about a patron whenever he checks out a book, we will instead give each library patron an ID, and repeat only the ID whenever we want to associate that person with a record in a different table.
Now the PATRONS and CHECKOUT tables can be related (how relationships are formally declared in various database software is beyond the scope of this paper).
At this point, we need some new terms to talk about our related tables. The primary key is a field whose values are unique in this table, and so can be used as identifiers for the records (multi-field or composite primary keys are beyond the scope of this paper, and are unlikely in an ArcGIS geodatabase). In table PATRONS, the Patron ID field is the primary key and so its values must remain unique. For example, the value “2” can appear only on one record – Alicia’s – and Alicia can have only one Patron ID – “2.” Is the Patron ID field in table CHECKOUT the primary key? We can see that it contains duplicate values, so the answer is No. If Patron ID were the primary key for CHECKOUT, each person would only be permitted to check out one book, and afterward would be forbidden to check out any more books, ever. So if Patron ID is not the primary key for table CHECKOUT, which field is? We can’t make Book Title the primary key, or we’d have a similar problem – each book could only be checked out once, and afterward no one would be permitted to check it out ever again. We can’t make Due Date the primary key, or else only one book could be due each day. Since none of the existing fields works as a primary key, we will add a new field to hold an identifier for each record. We could name this field Checkout ID, or we could follow ESRI’s convention of giving all primary key fields exactly the same name: ObjectID.
Naming every primary key field “ObjectID” does make it easy to tell at a glance which field uniquely identifies the records in this table. We can also use this naming convention to provide hints about which fields are related. For example, Patron ObjectID in CHECKOUT is related to ObjectID in PATRONS. To further increase efficiency, decrease required space, and improve ease of
maintenance, we can separate the book information into its own table.
Now ObjectID in BOOKS is related to Book ObjectID in CHECKOUT. When two tables have an unequal relationship, we call the independent table the parent and the dependent table the child. You can identify the parent table by determining which table could contain a record without needing a corresponding record in the table on the other side of the relationship. For example, is it possible to have an unpopular library book which never gets checked out? Yes. Is it possible to check out a book that doesn’t exist? No. Since BOOKS can contain records that aren’t referenced by CHECKOUT, BOOKS is the parent in this relationship, and CHECKOUT is the child. If somehow the child table contains a record that does not have a corresponding record in the parent table, that record is called an orphan. Orphaned records are a problem that generally requires attention from the database administrator. Another way to identify the child table is to find the field which refers to the other
table’s ObjectID. BOOKS does not contain an ObjectID field for the CHECKOUTS, but CHECKOUTS does contain a field to store Book ObjectIDs. Therefore, CHECKOUTS is the child table in this relationship. The last new concept to consider is cardinality, which describes how many records in one table can be related to records in another table. Two tables might have a cardinality of 1-1 (one to one), 1- ! (one to many), 1-3 (one to three), ! – ! (many to many), etc. The PATRONS – CHECKOUT relationship has a 1- ! cardinality, because 1 patron may have any number of checkouts, from zero to infinity. Put another way, the CHECKOUT – PATRONS relationship has a cardinality of ! – 1. If the cardinality of PATRONS – CHECKOUT were 1-1, then each patron could check out only one book. If it were ! – !, then several patrons together might share joint responsibility for one or more checkouts. The BOOKS – CHECKOUT relationship is also 1 – !, since one book may be checked out multiple times. If we really were designing the data model (tables, fields, relationships, etc.) for a library, we would continue by separating even more data (such as the authors) into other tables until the model was as efficient as possible. Since we are modeling utility data instead, let’s see how these ideas apply to meters and service points:
The SERVICE POINT table stores one record per location, but each location could have multiple Meters (for example, all the meters for an apartment building may be accessible from the same closet). The relationship between SERVICE POINT and METER is 1-! (one Service Point can include any number of meters). METER is the child table, because it contains a field to store Service Point ObjectIDs. Also notice that SERVICE POINT contains a Shape field, but METER does not. ArcGIS stores a map feature’s dimensions and location in the Shape field (although the specifics of this information may be hidden under the generic alias <Shape> ).
The presence of a Shape field in the SERVICE POINT table tells us that Service Points are features – objects that can be displayed on a map. The absence of a Shape field in METER tells us that Meters are ordinary objects that cannot be displayed on a map. When several related objects are likely to be in close proximity, having a Shape field only at the parent level decreases map clutter. For example, a high-rise apartment building might have one Service Point and 80 Meters. It is much more efficient to sketch one Service Point and then create 80 unmapped Meters related to that Service Point, than to sketch 80 individual Meters. In the same way, when sketching a three phase transformer, it is usually better to sketch one parent Transformer, which relates to three child Transformer Units (one per phase).
And now you know the fundamental database concepts of primary keys, parents, children, cardinality, and relationships, and their application in databases.
We are a professional custom writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework.
Yes. We have posted over our previous orders to display our experience. Since we have done this question before, we can also do it for you. To make sure we do it perfectly, please fill our Order Form. Filling the order form correctly will assist our team in referencing, specifications and future communication.
2. Fill in your paper’s requirements in the "PAPER INFORMATION" section and click “PRICE CALCULATION” at the bottom to calculate your order price.
3. Fill in your paper’s academic level, deadline and the required number of pages from the drop-down menus.
4. Click “FINAL STEP” to enter your registration details and get an account with us for record keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
5. From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.
Why Hire Safehomework.com writers to do your paper?