Competency G

Demonstrate understanding of basic principles and standards involved in organizing information such as classification and controlled vocabulary systems, cataloging systems, metadata schemas or other systems for making information accessible to a particular clientele

Without classification, data is not just irretrievable, but invisible. It must be tagged with data and stored. Those storage units are classification systems. Right now, in my garage, I have shoe boxes labeled: bolts, electrical, screws, plumbing, bike and safety located in four cupboards. In this analogy, the top tier or subject would be garage, followed by cupboard, ending with the shoebox: G.CB2.SHB 3. If I send my children to retrieve tools, I can tell them shoebox X in cupboard Y in the garage. The classification system is how the retrieval system (my children) knows where to go. However, classification systems have become complicated.

From Dewey classification to folksonomies and integrated library system mashups with outside sources like Amazon.com and WorldCat.org, classification has come far. Today, instead of bibliographers slotting information into predetermined subjects, taxonomy is increasingly created socially by users through keyword use. Clusters of keywords can be reflected in word clouds made of text that expresses keyword usage by its relative size. Users can narrow their search by repeatedly choosing the most relevant text (Cassel & Hiremath, 2013). The complexity of folksonomies demonstrates one of the applications of classification and controlled vocabulary.

Controlled vocabulary is used to index content. The index can be bifurcated into subcategories of preferred and variant terms. These assignments promote consistency by attaching the same terms to similar content. They gather synonyms and variant phrases together under preferred terms so that users who are not specialists can find information. Controlled vocabulary is used to tag information. These tags are called metadata.

Metadata is data about data. It provides location information, distinguishes dissimilar resources, identifies resources and provides the map for discovering information. It can include administrative data such as when it was created, by whom, and who has access to it. Some electronic data management software can use administrative metadata to automatically delete files of a certain age and report who has accessed a file. Metadata includes the underlying structural instructions that govern how a document is displayed or assembled. Metadata, controlled vocabulary and classification work together to make information available. They are essential for interoperability, forward and backward compatibility, system integration, sharing and storing data.

Classification became important to me as a teenager. The second place I went when I learned to drive was the book store at the University of Notre Dame. I bought Immanuel Kant’s Critique of Pure Reason, which introduced me to Kant’s twelve-tier classification; existence-nonexistence and possible- impossible, for example. Later I came to see library work as scaffolding that could extend our ability to think by storing and retrieving data instead of holding it in mind all at once. Now, we are confronted with a connected world that utterly depends on classification to retrieve data. Tomorrow, artificial intelligence will be used in ways I cannot even imagine. While not a natural bibliographer, classification is dear to my interests and work.

I enjoyed several courses that focus on Competency G skills. A required course, LIBR 202; Information Retrieval Design, began with an emphasis on the data structures that make information discoverable. We learned how to manipulate those structures to increase information’s accessibility. We studied natural search language and controlled vocabulary. Then, the course challenged us to view search engines, user behavior and interfaces through users’ perspectives. The detailed focus on users’ communication and information processing with information retrieval systems and the underlying taxonomies changed the way I think about search engines, Word files and integrated library systems (ILS), etcetera.

I learned to design a table of attributes and values. These formed the basic data structure for a large group project. The group project required my team to use indexing, controlled vocabulary and natural language to reach a hypothetical group of art buyers. We discussed the vocabulary the art community would use to search the database at length. I learned that testing the database can be the most rewarding and frustrating part of database building because it either works as intended or not. I found that very small differences in the metadata could yield catastrophic differences in results. My team worked together to solve the problems and enhance the variant terms. The next assignment was to apply those lessons to an in-depth research study of an information system of our choosing. I choose the Pueblo Public Library’s ILS.

INFO 284; Seminar in Archives and Records Management reinforced and built on the LIBR 202 lessons. I learned more uses for metadata; how software communicates with the data to perform a wider variety of functions than I thought possible. For example, I learned that discovery software can use the metadata to find deleted files between specific dates and by specific users. It can control who accesses files and who can alter them. I was continually surprised by new software’s ability to find information that I considered hidden or deleted. The coursework emphasized the historical and legal significance of data. The last half of the course emphasized the importance of coordinating metadata so that it could be read by future software updates. I learned to tag documents with automatic deletion dates, never delete, or encrypt instructions.

INFO 242; Database Design and Management tested my knowledge and provided a challenging experience with Oracle database-creation software: I learned to use structured query language (SQL) to communicate with various database management systems. First, the course provided background knowledge on database administration, management systems and querying. Then, we completed individual assignments. The group project followed. I will describe it in the evidence below. The course finished with a final exam to ensure that I absorbed the full spectrum of theoretical information on database history, design, management and query.

I submit “Records for Populating Tables” as my competency G evidence because the tables demonstrate my experience with classification through controlled vocabulary and metadata for a specific audience. The audience was the user group of a hypothetical business called, “Happy Face Dolls Inc.”. It was a group project created in several steps over three months. Usually, my groups divide responsibilities between members, but because of our unanimous unfamiliarity with database design, we just worked together on each piece.

Preparation: First, I completed readings and viewed lectures on the course content. The individual assignments formed a skills-based foundation for participating in the group project. This included creating a relational database model, learning how to add phase locks, and controlling for concurrency. The latter simply stops two people from altering a record at the same time if it will adversely affect the outcome e.g. as in financial transactions.

Execution: The first task was to create an entity-relationship (E-R) model of our client’s business. The group used google documents to create the work-products because the whole team had access. To ensure that we were on the right track, we discussed the first draft of the E-R model with the professor on Blackboard Collaborate. The E-R model began as a list of all the stakeholders and material categories involved with the project: Customer, recipient, order, personalization, stage, workstation, technician, parts, vendor and shipping. Then, we arranged them into a diagram that expressed their relationships. For example, doll is related to parts, shipping, order and recipient. Next, we built a data dictionary. The data dictionary describes each entity’s value and assigns it an abbreviation.

Then we normalized the model. Normalizing is the multi-step process of identifying data groups that will be repeated often and storing repeated data as separate entities. This makes the diagram flexible and efficient. The second normalizing step is to diagram the relationships between the values and entities. We created new entities for any information that repeated because having the information in multiple places puts it at risk of not being updated uniformly and slowing the database. The relationships can be many to many, many to one, one to many and one to one. All the entities must be unique.

Next, we articulated each value as an SQL readable data point so that we could write the SQL for querying the Oracle database management software. The tables below comprise the evidence of this endeavor. The first set of information consists of the entities. Each entity has its own table of values. For example, the customer values include their name, address, e-mail, phone number, credit card number, credit card expiration and customer code. The customer code is the primary key, which means a user only needs the customer code to access the rest of the entry. Some examples of entity values and their translations follow: customer last name = cus_ lname, right arm = rarm, and srhair = strawberry red hair. Some examples of the SQL terms used in the tables include: VARCHAR which means a user can enter a variable number of characters into that field. VARCHAR is followed by a number in parenthesis. This limits the number of characters that can be entered. NOT NULL is used to signify that something must be entered; the field cannot be skipped or left blank.

Then, we entered the tables into Oracle. Last, we tested our work which meant rewriting the Records for Populating Tables until all our client-centered queries worked. Queries included tasks such as: sorting parts by vendor address, reporting which technicians worked at which workstations on specific dates, which technicians had birthdays in January, and which customers bought three or more dolls? Completing this project and posting The Records for Populating Tables, shows competence because the records are meticulously formatted metadata of classes of information using controlled vocabulary that were tested for the needs of a specific clientele group: Happy Face Doll Inc.

Not only did I learn about classification, controlled vocabulary, classification systems and metadata schemas for different clientele: I learned about teamwork. While I had worked in many teams during the MLIS process at SJSU, I had not experienced being the follower who needed help until INFO 242. My teammates had distinct skills that manifested in high quality work and effective student teaching. They exhibited patience and aptitude for education. I learned how to ask questions and communicate problems.

Before the competency G coursework and the database project, my understanding of metadata was thin. I didn’t understand the way OPECs, LISs, databases and search tools connected user search terms to stored information. I learned how databases use metadata to reveal information, and likewise, how information retrieval systems use data.

This knowledge will help me adapt to new technologies because I will understand how they operate, what I can do to communicate with them, and how I can teach users to search them. Evaluating taxonomies for specific clientele, such as art critics and doll factory administrators, has expanded my ability to design effective controlled vocabularies. I look forward to synthesizing these lessons by securing and preserving data, evaluating database management software and administering databases in my future workplace.

References

Cassell K. A., Hiremath U. (2013). Reference and information services. Neal-Schuman. Chicago, IL.

Evidence

-Records for populating tables

drop table CUSTOMER;

drop table ORDER;

drop table PERSONALIZATION;

drop table DOLL;

drop table PART;

drop table VENDOR;

drop table PART_TYPE;

drop table STAGE;

drop table WORKSTATION;

drop table TECHNICIAN;

drop table SHIPMENT;

drop table RECIPIENT;

create table CUSTOMER (

customer_code NUMBER (5, 0) NOT NULL,

cus_lname VARCHAR(20) NOT NULL,

cus_fname VARCHAR(20) NOT NULL,

cus_address VARCHAR(40) NOT NULL,

cus_phone NUMBER (10, 0) NOT NULL,

cus_email VARCHAR(30) NOT NULL,

cus_ccard NUMBER (16, 0) NOT NULL,

cus_ccardexp DATE NOT NULL,

PRIMARY KEY (customer_code)

);

Insert into CUSTOMER values (‘00001’, ‘Smith’, ‘John’, ‘256 Fox Trail, New Carlisle, IN 46552’, ‘902-999-9283’, ‘john@hmail.com’, 2223222322232223’, ‘09/23’);

Insert into CUSTOMER values (‘00002’, ‘Hart’, ‘Jason’, ‘56 Blue Lane, South Bend, IN 46552’, ‘987-922-2344’, ‘jason@jmail.com’, ‘4325009388439876’, ‘09/22’);

Insert into CUSTOMER values (‘00003’, ‘Johnson’, ‘Rebecca’, ‘555 New Road, Kokomo, IN 46222’, ‘754-333-3984’, ‘becca@ghrmail.com’, ‘8374837493873849’, ‘08/25’);

Insert into CUSTOMER values (‘00004’, ‘Bateman’, ‘Jason’, ‘41 Rainbow Ave., Idaho Springs, CO 89421’, ‘223-304-0983’, ‘batmen@bat.com’, ‘6011837464833999’, ‘04/19’);

Insert into CUSTOMER values (‘00005’, ‘Miller’, ‘Abe’, ‘5100 Illiff Ave, Denver, CO 82222’, ‘456-456-4532’, ‘abe@lincoln.com’, ‘8765876587658765’, ‘02/21’);

Insert into CUSTOMER values (‘00006’, ‘Mackie’, ‘Frankie’, ‘546 Westton Street, Kilpatrick, KY 39412’, ‘765-348-0947’, ‘Frank@iemonkey.com’, ‘3324222344432222’, ‘01/18’);

Insert into CUSTOMER values (‘00007’, ‘Dohr’, ‘Jeanie’, ‘222 Sparrow Way, Covington, NM 76333’, ‘987-343-0987’, ‘Dohr@iemonkey.com’, ‘7654857694033334’, ‘02/18’);

Insert into CUSTOMER values (‘00008’, ‘Ying’, ‘Jing’, ‘987 4th Ave Houston, TX 23745’, ‘947-353-7787’, ‘jing@iemonkey.com’, ‘7575757483384742’, ‘12/18’);

Insert into CUSTOMER values (‘00009’, ‘Land’, ‘Joe’, ‘90 Grand Blvd, Laramie, WY 59101’, ‘937-346-0987’, ‘joe@iemonkey.com’, ‘1236987548789856’, ‘11/18’);

Insert into CUSTOMER values (‘00010’, ‘Massey’, ‘Jon’, ‘23 Wilbur Way, Shireville, MT 60832’, ‘187-377-0947’, ‘massey@iemonkey.com’, ‘2587413698521478’, ‘12/18’);

Insert into CUSTOMER values (‘00011’, ‘Jackson’, ‘Jake’, ‘1000 Sheridan Ave, Spirit, MO 69321’, ‘787-345-0787’, ‘jakej@iemonkey.com’, ‘1346798521236548’, ‘10/18’);

Insert into CUSTOMER values (‘00012’, ‘Lennon’, ‘Julian’, ‘451 Wax Street, Ono, MI 45111’, ‘357-373-0387’, ‘juiles@iemonkey.com’, ‘2587412369877777’, ‘08/18’);

Insert into CUSTOMER values (‘00013’, ‘Marx’, ‘Janice’, ‘901 Walsh Street, Fabien, CA 90210’, ‘777-349-0787’, ‘marx@iemonkey.com’, ‘1258745963258741’, ‘09/18’);

Insert into CUSTOMER values (‘00014’, ‘Builtman’, ‘Wendy’, ‘1976 Forest Drive, South Bend, IN 46552’, ‘327-343-0977’, ‘wendywoo@iemonkey.com’, ‘1202587014563210’, ‘08/18’);

Insert into CUSTOMER values (‘00015’, ‘Bateman’, ‘Bill’, ‘88 Loch Lomond, South Wellington, NZ 00001’, ‘577-373-1187’, ‘billyjoelnotreally@iemonkey.com’, ‘1895674123659874’, ‘04/17’);

Insert into CUSTOMER values (‘00016’, ‘Broke’, ‘Joke’, ‘65387 Masters Drive, Olympia, WA 98000’, ‘977-943-4787’, ‘brokemyfoot@iemonkey.com’, ‘2014000036982547’, ‘01/18’);

Insert into CUSTOMER values (‘00017’, ‘Joplin’, ‘Janice’, ‘900 Felt Way, Gouda, OR 98002’, ‘998-343-7977’, ‘janice@iemonkey.com’, ‘3324222341231622’, ‘01/19’);

Insert into CUSTOMER values (‘00018’, ‘Jones’, ‘Jesus’, ‘234 Farmer, Williamsburg, VA 23461’, ‘955-353-0977’, ‘jonesj@iemonkey.com’, ‘3333666699998888’, ‘01/21’);

Insert into CUSTOMER values (‘00019’, ‘Marriot’, ‘Martin’, ‘8 Picador Way, Billings, MT 59105’, ‘741-743-0976’, ‘martin@iemonkey.com’, ‘1010236587001478’, ‘01/20’);

Insert into CUSTOMER values (‘00020’, ‘Noir’, ‘Guy’, ‘431 Martin Blvd, New York, NY 10203’, ‘221-344-1237’, ‘thatoneguynamednoir@iemonkey.com’, ‘3324223698432222’, ‘01/19’);

create table ORDER (

order_numb NUMBER (5, 0) NOT NULL,

order_date DATE NOT NULL,

order_expdate DATE,

order_quantity NUMBER (2, 0),

dol_id NUMBER (5, 0) NOT NULL,

cus_code (5, 0) NOT NULL,

PRIMARY KEY (order_numb),

FOREIGN KEY (dol_id) REFERENCES doll,

FOREIGN KEY (cus_code) REFERENCES customer

);

Insert into ORDER values (‘00001’, ‘01-02-2017’, ‘01-30-2017’, ‘2’, ‘00001’, ‘00001’);

Insert into ORDER values (‘00002’, ‘01-02-2017’, ‘01-30-2017’, ‘1’, ‘00002’, ‘00002’);

Insert into ORDER values (‘00003’, ‘01-02-2017’, ‘01-30-2017’, ‘3’, ‘00003’, ‘00003’);

Insert into ORDER values (‘00004’, ‘01-02-2017’, ‘01-30-2017’, ‘1’, ‘00004’, ‘00004’);

Insert into ORDER values (‘00005’, ‘01-03-2017’, ‘01-31-2017’, ‘1’, ‘00005’, ‘00005’);

Insert into ORDER values (‘00006’, ‘01-03-2017’, ‘01-31-2017’, ‘1’, ‘00006’, ‘00006’);

Insert into ORDER values (‘00007’, ‘01-03-2017’, ‘01-31-2017’, ‘1’, ‘00007’, ‘00007’);

Insert into ORDER values (‘00008’, ‘01-03-2017’, ‘01-31-2017’, ‘1’, ‘00008’, ‘00008’);

Insert into ORDER values (‘00009’, ‘01-03-2017’, ‘01-31-2017’, ‘2’, ‘00009’, ‘00009’);

Insert into ORDER values (‘00010’, ‘01-03-2017’, ‘01-31-2017’, ‘1’, ‘00010’, ‘00010’);

Insert into ORDER values (‘00011’, ‘01-04-2017’, ‘02-01-2017’, ‘2’, ‘00011’, ‘00011’);

Insert into ORDER values (‘00012’, ‘01-04-2017’, ‘02-01-2017’, ‘1’, ‘00012’, ‘00012’);

Insert into ORDER values (‘00013’, ‘01-04-2017’, ‘02-01-2017’, ‘2’, ‘00013’, ‘00013’);

Insert into ORDER values (‘00014’, ‘01-04-2017’, ‘02-01-2017’, ‘1’, ‘00014’, ‘00014’);

Insert into ORDER values (‘00015’, ‘01-04-2017’, ‘02-01-2017’, ‘1’, ‘00015’, ‘00015’);

Insert into ORDER values (‘00016’, ‘01-04-2017’, ‘02-01-2017’, ‘1’, ‘00016’, ‘00016’);

Insert into ORDER values (‘00017’, ‘01-04-2017’, ‘02-01-2017’, ‘1’, ‘00017’, ‘00017’);

Insert into ORDER values (‘00018’, ‘01-05-2017’, ‘02-02-2017’, ‘1’, ‘00018’, ‘00018’);

Insert into ORDER values (‘00019’, ‘01-05-2017’, ‘02-02-2017’, ‘1’, ‘00019’, ‘00019’);

Insert into ORDER values (‘00020’, ‘01-05-2017’, ‘02-02-2017’, ‘2’, ‘00020’, ‘00020’);

create table PERSONALIZATION (

per_name CHAR(20) NOT NULL,

per_birthday DATE NOT NULL,

per_size VARCHAR(3) NOT NULL,

per_gender VARCHAR(1) NOT NULL,

per_style CHAR(10) NOT NULL,

per_haircolor CHAR(10) NOT NULL,

per_eyecolor CHAR(10) NOT NULL,

per_complexion CHAR(10) NOT NULL,

per_garment VARCHAR(10) NOT NULL,

per_shoestyle CHAR(10) NOT NULL,

order_numb NUMBER (5, 0) NOT NULL,

PRIMARY KEY (per_name),

FOREIGN KEY (order_numb) REFERENCES order

);

INSERT INTO PERSONALIZATION VALUES(‘Tommie’, ‘11-29-2015’, ‘S’, ‘M’, ‘80s’, ‘Chestnut Brown’, ‘Hazel’, ‘Chestnut Brown’, ‘Playclothes’, ‘Casual’, 00001);

INSERT INTO PERSONALIZATION VALUES(‘Annabelle’, ‘12-02-16’, ‘S’, ‘F’, ‘NULL’,

‘Strawberry’, ‘Green’, ‘Sand’, ‘White Dress’, ‘White Shoes’, 00002);

INSERT INTO PERSONALIZATION VALUES(‘Jenna’, ‘05-13-2013’, ‘M’, ‘F’, ‘Farmer’, ‘Coffee Brown’, ‘Green’, ‘Chestnut’, ‘Playclothes’, 00003);

INSERT INTO PERSONALIZATION VALUES(‘Steve’, ‘06-20-16’, ‘M’, ‘M’, ‘Astronaut’ ‘Coffee Brown’, ‘Green’, ‘Chestnut’, ‘Tuxedo’, 00004);

INSERT INTO PERSONALIZATION VALUES(‘Connie_Sue’, ‘01’20’17’, ‘S’, ‘F’, ‘Farmer’, ‘Chestnut Brown’, ‘Hazel’, ‘Peach’, ‘Playclothes’, ‘White Shoes’, 00005);

INSERT INTO PERSONALIZATION VALUES(‘Suzy_Q’, ‘10-01-15’, ‘M’, ‘F’, ‘80s’, ‘Strawberry Red’, ‘Blue’, ‘Sand’, ‘Red Dress’, ‘Red Shoes’, 00006);

INSERT INTO PERSONALIZATION VALUES(‘Daniel’, ‘03-14-17’, ‘S’, ‘M’, ‘NULL’, ‘Strawberry Red’, ‘Green’, ‘Peach’, ‘White Dress’, ‘White Shoes’, 00007);

INSERT INTO PERSONALIZATION VALUES(‘Wendy_Sue’, ‘07-09-16’, ‘M’, ‘F’, ‘Casual’, ‘Chestnut Brown’, ‘Blue’, ‘Chestnut Brown’, ‘Playclothes’, ‘White Shoes’, 00008);

INSERT INTO PERSONALIZATION VALUES(‘Brandon’, ‘02-16-17’, ‘S’, ‘M’, ‘NULL’, “Chestnut Brown’, ‘Blue’, ‘Chestnut Brown’, ‘Tuxedo’, ‘Black Shoes’, 00009);

INSERT INTO PERSONALIZATION VALUES(‘Jennifer’, ‘08-12-15’, ‘M’, ‘F’, ‘NULL’, ‘Coffee Brown’, ‘Green’, ‘Coffee Brown’, ‘Red Dress’, ‘Red Shoes’, 00010);

INSERT INTO PERSONALIZATION VALUES(‘Katherine’, ‘10-09-14’, ‘S’, ‘F’, ‘NULL’, ‘Strawberry Red’, ‘Hazel’, ‘Sand’, ‘White Dress’, ‘White Shoes’, 00011);

INSERT INTO PERSONALIZATION VALUES(‘Tai’, ‘02-14-15’, ‘M’, ‘F’, ‘NULL’, ‘Chestnut Brown’, ‘Blue’, ‘Peach’, ‘White Dress’, ‘White Shoes’, 00012);

INSERT INTO PERSONALIZATION VALUES(‘Suzanne’, ‘01-29-14’, ‘S’, ‘F’, “80s’, ‘Coffee Brown’, ‘Blue’, “Chestnut Brown’, ‘Playclothes’, ‘Red Shoes’, 00013);

INSERT INTO PERSONALIZATION VALUES(‘Mister_Wiggles’, ‘11-06-16’, ‘M’, ‘M’, ‘NULL’, ‘Strawberry Red’, ‘Green’, ‘Coffee Brown’, ‘Playclothes’, ‘Red Shoes’, 00014);

INSERT INTO PERSONALIZATION VALUES(‘Marianne’, ‘03-30-17’, ‘S’, ‘F’, ‘NULL’, ‘Chestnut Brown’, ‘Hazel’, ‘Sand’, ‘Red Dress’, ‘Red Shoes’, 00015);

INSERT INTO PERSONALIZATION VALUES(‘Marshall’, ‘05-31-15’, ‘M’, ‘M’, ‘NULL’, ‘Coffee Brown’, ‘Blue’, ‘Peach’, ‘Playclothes’, “White Shoes’, 00016);

INSERT INTO PERSONALIZATION VALUES(‘Marsha’, ‘10-02-12’, ‘S’, ‘F’, ‘NULL’, ‘Strawberry Red’, ‘Green’, ‘Chestnut Brown’, ‘White Dress’, ‘White Shoes’, 00017);

INSERT INTO PERSONALIZATION VALUES(‘Michael’, ‘02-02-14’, ‘M’, ‘M’, ‘NULL’, ‘Chestnut Brown’, ‘Hazel’, ‘Sand’, ‘Tuxedo’, ‘Black Shoes’, 00018);

INSERT INTO PERSONALIZATION VALUES(‘Allison’, ‘07-23-15’, ‘S’, ‘F’, ‘90s Grunge’, ‘Coffee Brown’, ‘Hazel’, ‘Coffee Brown’, ‘Red Dress’, ‘Black Shoes’, 00019);

INSERT INTO PERSONALIZATION VALUES(‘Mariam’, ‘12-31-16’, ‘S’, ‘F’, ‘NULL’, ‘Coffee Brown’, ‘Green’, Coffee Brown’, ‘White Dress’, ‘White Shoes’, 00020);

create table DOLL (

dol_id NUMBER (5, 0) NOT NULL,

dol_head CHAR(5),

dol_hair CHAR(5),

dol_eyes CHAR(5),

dol_body CHAR(5),

dol_ra CHAR(5),

dol_la CHAR(5),

dol_rl CHAR(5),

dol_ll CHAR(5),

dol_garm CHAR(5),

dol_shoe CHAR(5),

dol_name VARCHAR(20) NOT NULL,

part_code NUMBER (5,0) NOT NULL,

ship_number NUMBER (5,0) NOT NULL,

PRIMARY KEY (dol_id),

FOREIGN KEY (part_code) REFERENCES part_code,

FOREIGN KEY (ship_number) REFERENCES shipment

);

INSERT INTO DOLL VALUES(‘00001’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Pants’, ‘Black’, ‘Tommie’, ‘22222’, 00001);

INSERT INTO DOLL VALUES(‘00002’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘White’, ‘Annabelle’, ‘32222’, 00002);

INSERT INTO DOLL VALUES(‘00003’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘Reddd’, ‘Jenna’, ‘41111’, 00003);

INSERT INTO DOLL VALUES(‘00004’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Pants’, ‘Black’, ‘Steve’, ‘52222’, 00004);

INSERT INTO DOLL VALUES(‘00005’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘White’, ‘Connie_Sue’, ‘52223’, 00005);

INSERT INTO DOLL VALUES(‘00006’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘Reddd’, Suzy_Q’, ‘32221’, 00006);

INSERT INTO DOLL VALUES(‘00007’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Pants’, ‘Black’, ‘Daniel’, ‘32211’, 00007);

INSERT INTO DOLL VALUES(‘00008’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘White’, ‘Wendy_Sue’, ‘22211’, 00008);

INSERT INTO DOLL VALUES(‘00009’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Pants’, ‘Black’, ‘Brandon’, ‘00001’, 00009);

INSERT INTO DOLL VALUES(‘00010’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘Reddd’, ‘Jennifer’, ‘00002’, 00010);

INSERT INTO DOLL VALUES(‘00011’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘White’, ‘Katherine’, ‘00012’, 00011);

INSERT INTO DOLL VALUES(‘00012’, ‘Head’, Hair’, ‘Eyes’, ‘Body’, Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘White’, ‘Tai’, ‘00013’, 00012);

INSERT INTO DOLL VALUES(‘00013’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘Reddd’, ‘Suzanne’, ‘00014’, 00013);

INSERT INTO DOLL VALUES(‘00014’, ‘Head’, Hair’, ‘Eyes’, ‘Body’, Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Pants’, ‘Reddd’, ‘Mister_Wiggles’, ‘00015’, 00014);

INSERT INTO DOLL VALUES(‘00015’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘White’, ‘Marianne’, ‘00016’, 00015);

INSERT INTO DOLL VALUES(‘00016’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Pants’, ‘Black’, ‘Marshall’, ‘00017’, 00016);

INSERT INTO DOLL VALUES(‘00017’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘White’, ‘Marsha’, ‘00018’, 00017);

INSERT INTO DOLL VALUES(‘00018’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Pants’, ‘Black’, ‘Michael’, ‘00019’, 00018);

INSERT INTO DOLL VALUES(‘00019’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Dress’, ‘Reddd’, ‘Allison’, ‘20000’, 00019);

INSERT INTO DOLL VALUES(‘00020’, ‘Head’, ‘Hair’, ‘Eyes’, ‘Body’, ‘Rarm’, ‘Larm’, ‘Rleg’, ‘Lleg’, ‘Pants’, ‘Black’, ‘Mariam’, ‘21000’, 00020);

create table PART (

ven_name VARCHAR(20) NOT NULL,

PRIMARY KEY (part_code),

FOREIGN KEY (ven_name) REFERENCES vendor

);

INSERT INTO PART VALUES (‘22222’, Denver Plastics);

INSERT INTO PART VALUES (‘32222’, Denver Plastics);

INSERT INTO PART VALUES (‘41111’, Denver Plastics);

INSERT INTO PART VALUES (‘52222’, XYZ Glass);

INSERT INTO PART VALUES (‘52223’, XYZ Glass);

INSERT INTO PART VALUES (‘32221’, Wyoming Poly);

INSERT INTO PART VALUES (‘32211’, Wyoming Poly);

INSERT INTO PART VALUES (‘22221’, Wyoming Poly);

INSERT INTO PART VALUES (‘00001’, Wyoming Poly);

INSERT INTO PART VALUES (‘00002’, Ontario Cotton);

INSERT INTO PART VALUES (‘00012’, Ontario Cotton);

INSERT INTO PART VALUES (‘00013’, Ontario Cotton);

INSERT INTO PART VALUES (‘00014’, Ontario Cotton);

INSERT INTO PART VALUES (‘00015’, Resin Master);

INSERT INTO PART VALUES (‘00016’, Resin Master);

INSERT INTO PART VALUES (‘00017’, Ontario Cotton);

INSERT INTO PART VALUES (‘00018’, Ontario Cotton);

INSERT INTO PART VALUES (‘00019’, Wyoming Poly);

INSERT INTO PART VALUES (‘20000’, Wyoming Poly);

INSERT INTO PART VALUES (‘21000’, Wyoming Poly);

INSERT INTO PART VALUES (‘22000’, Denver Plastics);

INSERT INTO PART VALUES (‘23000’, Denver Plastics);

INSERT INTO PART VALUES (‘24000’, Denver Plastics);

INSERT INTO PART VALUES (‘25000’, Ontario Cotton);

INSERT INTO PART VALUES (‘26000’, Ontario Cotton);

INSERT INTO PART VALUES (‘27000’, Ontario Cotton);

INSERT INTO PART VALUES (‘28000’, Ontario Cotton);

INSERT INTO PART VALUES (‘29000’, Denver Plastics);

INSERT INTO PART VALUES (‘31000’, Denver Plastics);

INSERT INTO PART VALUES (‘32000’, Denver Plastics);

INSERT INTO PART VALUES (‘33000’, Denver Plastics);

INSERT INTO PART VALUES (‘34000’, Resin Masters);

INSERT INTO PART VALUES (‘35000’, Resin Masters);

create table VENDOR (

ven_name VARCHAR(20) NOT NULL,

ven_partname CHAR(8) NOT NULL,

ven_price NUMBER (4,2) NOT NULL,

ven_discount NUMBER (4,2),

ven_rating VARCHAR(5),

mat_code NUMBER (5,0) NOT NULL,

PRIMARY KEY (ven_name),

FOREIGN KEY (mat_code) REFERENCES part_type

);

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘CLBHead’, ‘0185’, ‘0010’, ‘55554’, 22222 );

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘CMCHead’, ‘2000’, ‘0010’, ‘55554’, 32222 );

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘CBTHead’, ‘0195’, ‘0010’, ‘55554’, 41111 );

INSERT INTO VENDOR VALUES (‘XYZ Glass’, ‘BBeyes’, ‘0090’, ‘0002’, ‘66666’, 52222 );

INSERT INTO VENDOR VALUES (‘XYZ Glass’, ‘BCeyes’, ‘0090’, ‘0002’, ‘66666’, 52223 );

INSERT INTO VENDOR VALUES (‘Wyoming Poly’, ‘CLBarm’, ‘0125’, ‘0001’, ‘77777’, 32221 );

INSERT INTO VENDOR VALUES (‘Wyoming Poly’, ‘CRBarm’, ‘0125’, ‘0001’, ‘77777’, 32211 );

INSERT INTO VENDOR VALUES (‘Wyoming Poly’, ‘CLCleg’, ‘0125’, ‘0001’, ‘77777’, 22221 );

INSERT INTO VENDOR VALUES (‘Wyoming Poly’, ‘CRCleg’, ‘0130’, ‘0001’, ‘77777’, 00001 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘SSBT’, ‘0325’, ‘0025’, ‘99999’, 00002 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘SMCT’, ‘0325’, ‘0025’, ‘99999’, 00012 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘CBSBT’, ‘0325’, ‘0025’, ‘99999’, 00013 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘CBMCT’, ‘0325’, ‘0025’, ‘99999’, 00014 );

INSERT INTO VENDOR VALUES (‘Resin Master’, ‘GSBEyes’, ‘0085’, ‘0001’, ‘44444’, 00015 );

INSERT INTO VENDOR VALUES (‘Resin Master’, ‘GMCEyes’, ‘0085’, ‘0001’, ‘44444’, 00016 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘PSBT’, ‘0400’, ‘0025’, ‘99999’, 00017 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘PMCT’, ‘0400’, ‘0025’, ‘99999’, 00018 );

INSERT INTO VENDOR VALUES (‘Wyoming Poly’, ‘PSBH’, ‘0185’, ‘0001’, ‘77777’, 00019 );

INSERT INTO VENDOR VALUES (‘Wyoming Poly’, ‘PMCH’, ‘0325’, ‘0001’, ‘77777’, 20000 );

INSERT INTO VENDOR VALUES (‘Wyoming Poly’, ‘PSLBA’, ‘0350’,’0001’, ‘77777’, 21000 );

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘CBHair’, ‘0050’, ‘0001’, ‘55554’, 22000 );

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘SRHair’, ‘0050’, ‘0001’, ‘55554’, 23000 );‘

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘CBHair’, ‘0050’, ‘0001’, ‘55554’, 24000 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘RCDress’, ‘0300’, ‘0025’, ‘99999’, 25000 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘WBDress’, ‘0300’, ‘0025’, ‘99999’, 26000 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘BCTux’, ‘0400’, ‘0025’, ‘99999’, 27000 );

INSERT INTO VENDOR VALUES (‘Ontario Cotton’, ‘BBPlayc’, ‘0200’, ‘0025’, 99999’, 28000 );

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘WBGShoes’, ‘0075’, ‘0025’, ‘55554’, 29000 );

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘BBBShoes’, ‘0075’, ‘0025’, ‘55554’, 31000 );

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘WCGShoes’, ‘0075’, ‘0025’, ‘55554’, 32000 );

INSERT INTO VENDOR VALUES (‘Denver Plastics’, ‘BCBShoes’, ‘0075’, ‘0025’, ‘55554’, 33000 );

INSERT INTO VENDOR VALUES (‘Resin Masters’, ‘HCEyes’, ‘0125’, ‘0001’, ‘44444’, 34000);

INSERT INTO VENDOR VALUES (‘Resin Masters’, ‘HBEyes’, ‘0125’, ‘0001’, ‘44444’, 35000);

create table PART_TYPE (

mat_code NUMBER (5, 0) NOT NULL,

mat_color CHAR(20),

mat_size VARCHAR(4),

mat_desc CHAR(20),

mat_feature CHAR(20),

mat_material CHAR(20),

PRIMARY KEY (mat_code)

);

INSERT INTO PART_TYPE VALUES (‘22222’, ‘Chestnut Brown’, ‘S’, ‘Baby Head’, ‘Smile’, Polymer );

INSERT INTO PART_TYPE VALUES (‘32222’, ‘Chestnut Brown’, ‘M’, ‘Child Head’, ‘Toothy Smile’, Polymer );

INSERT INTO PART_TYPE VALUES (‘41111’, ‘Chestnut Brown’, ‘L’, ‘Teen Head’, ‘Mischievous Smile’, Polymer );

INSERT INTO PART_TYPE VALUES (‘52222’, ‘Blue’, ‘S’, ‘Baby Eyes’, ‘Layered’, Glass );

INSERT INTO PART_TYPE VALUES (‘52223’, ‘Blue’, ‘M’, ‘Child Eyes’, ‘Reflecting’, Glass );

INSERT INTO PART_TYPE VALUES (‘32221’, ‘Coffee’, ‘S’, ‘Left Baby Arm’, ‘Rotating’, Polymer );

INSERT INTO PART_TYPE VALUES (‘32211’, ‘Coffee’, ‘S’, ‘Right Baby Arm’, ‘Rotating’, Polymer );

INSERT INTO PART_TYPE VALUES (‘22221’, ‘Coffee’, ‘M’, ‘Left Child Leg’, ‘Hinged Knee’, Polymer );

INSERT INTO PART_TYPE VALUES (‘00001’, ‘Coffee’, ‘M’, ‘Right Child Leg’, ’Hinged Knee’, Polymer );

INSERT INTO PART_TYPE VALUES (‘00002’, ‘Sand’, ‘S’, ‘Baby Torso’, ‘Plush’, Cotton );

INSERT INTO PART_TYPE VALUES (‘00012’, ‘Sand’, ‘M’, ‘Child Torso’, ‘Plush”, Cotton );

INSERT INTO PART_TYPE VALUES (‘00013’, ‘Chestnut Brown’, ‘S’, ‘Baby Torso’, ‘Plush’, Cotton );

INSERT INTO PART_TYPE VALUES (‘00014’, ‘Chestnut Brown’, ‘M’, ‘Child Torso’, ‘Plush’, Cotton );

INSERT INTO PART_TYPE VALUES (‘00015’, ‘Green’, ‘S’, ‘Baby Eyes’, ‘Blinking’, Resin );

INSERT INTO PART_TYPE VALUES (‘00016’, ‘Green’, ‘M’, ‘Child Eyes’, ‘Blinking’, Resin );

INSERT INTO PART_TYPE VALUES (‘00017’, ‘Peach’, ‘S’, ‘Baby Torso’, ‘Plush’, Cotton );

INSERT INTO PART_TYPE VALUES (‘00018’, ‘Peach’, ‘M’, ‘Child Torso’, ‘Plush’, Cotton );

INSERT INTO PART_TYPE VALUES (‘00019’, ‘Peach’, ‘S’, ‘Baby Head’, ‘Grin’, Polymer );

INSERT INTO PART_TYPE VALUES (‘20000’, ‘Peach’, ‘M’, ‘Child Head’, ‘Toothy Smile’, Polymer );

INSERT INTO PART_TYPE VALUES (‘21000’, ‘Peach’, ‘S’, ‘Left Baby Arm’, ‘No Hinge’, Polymer );

INSERT INTO PART_TYPE VALUES(‘22000’, ‘Chestnut Brown’, ‘NULL’, ‘Straight Hair’, ‘’Ponytail’, Nylon);

INSERT INTO PART_TYPE VALUES(‘23000’, ‘Strawberry Red’, ‘NULL’, ‘Wavy Hair’, ‘Half Up’, Nylon);

INSERT INTO PART_TYPE VALUES(‘24000’, ‘Coffee Brown’, ‘NULL’, ‘Curly Hair’, ‘Two Buns’, Nylon);

INSERT INTO PART_TYPE VALUES(‘25000’, ‘Red’, ‘Child’, ‘Dress’, ‘Formal’, Polyester);

INSERT INTO PART_TYPE VALUES(‘26000’, ‘White’, ‘Baby’, ‘Dress’, ‘Casual’, Rayon);

INSERT INTO PART_TYPE VALUES(‘27000’, ‘Black’, ‘Child’, ‘Tuxedo’, ‘Formal’, Polyester);

INSERT INTO PART_TYPE VALUES(‘28000’, ‘Blue’, ‘Baby’, ‘Playclothes’, ‘Casual’, Cotton);

INSERT INTO PART_ TYPE VALUES(‘29000’, ‘White’, ‘Baby’, ‘Girl Shoes’, ‘NULL’, Plastic);

INSERT INTO PART_TYPE VALUES(‘31000’, ‘Black’, ‘Baby’, ‘Boy Shoes’, ‘NULL’, Plastic);

INSERT INTO PART_ TYPE VALUES(‘32000’, ‘White’, ‘Child’, ‘Girl Shoes’, ‘NULL’, Plastic);

INSERT INTO PART_TYPE VALUES(‘33000’, ‘Black’, ‘Child’, ‘Boy Shoes’, ‘NULL’, Plastic);

INSERT INTO PART_TYPE VALUES(‘34000’, ‘Hazel’, ‘Child’, ‘Eyes’, ‘Blinking’, Resin);

INSERT INTO PART_TYPE VALUES(‘35000’, ‘Hazel’, ‘Baby’, ‘Eyes’, ‘Blinking’, Resin);

create table STAGE (

stage_number NUMBER (4, 0) NOT NULL,

stage_code NUMBER (2,0) NOT NULL,

work_id (5,0) NOT NULL

dol_id (5,0) NOT NULL,

PRIMARY KEY (stage_number),

FOREIGN KEY (work_id) REFERENCES workstation,

FOREIGN KEY (dol_id) REFERENCES doll

);

INSERT INTO STAGE VALUES (‘00011’, ‘21’, ‘00001’, 09999 );

INSERT INTO STAGE VALUES (‘00022’, ‘22’, ‘00002’, 08888 );

INSERT INTO STAGE VALUES (‘00033’, ‘23’, ‘00003’, 07777 );

INSERT INTO STAGE VALUES (‘00044’, ‘24’, ‘00004’, 06666 );

INSERT INTO STAGE VALUES (‘00055’, ‘25’, ‘00005’, 05555 );

INSERT INTO STAGE VALUES (‘00066’, ‘26’, ‘00006’, 04444 );

create table WORKSTATION (

work_id NUMBER (5, 0) NOT NULL,

work_order CHAR(2) NOT NULL,

work_design CHAR(2) NOT NULL,

work_assembly CHAR(2) NOT NULL,

work_custom CHAR(2) NOT NULL,

work_quality CHAR(2) NOT NULL,

work_shipping CHAR(2) NOT NULL,

PRIMARY KEY (work_id)

);