MGMT 3120 Database Management and Theory

1. MGMT 3120 Database Management and Theory Younghwa Gabe Lee Associate Professor of Information Systems

2. Class Introduction What is Database? Syllabus Overview Who is Gabe Lee ?

3. Database • The world today… • An organized collection of logically related data. • Database is pervasive across industry • Clinic (Patient, Doctor, Appointment, Medical Records, Accounting) • Barnes & Nobles (Customers, Books, Sales) • Airline Companies (Aircraft, Attendance, Customers, Flight Schedule, Sales) • Dealership (Customers, Sales, Salesperson, Cars, Financing) • Support a wide-range of business operations in Accounting, Finance, HR, Marketing, Inventory Control, Forecasting, and Sales.

4. Database • An enormous amount of data is produced everyday. • 2 exabytes (1018 bytes) in 1999, 246 exabytes in 2007 • 988 exabytes in 2011 (e.g. 5 exabytes: All words ever spoken) • Managers seek to use knowledge derived for data for competitive advantage . • Need information systems that help easily create, store, update, and delete data and quickly retrieve desired pieces of the stored data(i.e. database) • However, only a few companies enjoy the benefits of database e.g. - Sherwin-Williams: Search paints used only by painter’s name • UPS: Cannot add the full 9 digit zip code (99999) instead of (99999-9999)

5. Database • Course Objective : Help you have a better understanding of designing, implementing, and maintaining effective and efficient database(s). • Developed following DDLC • Learn about the activities conducted in each phase during the semester • Focus on Analysis, Design and Implementation phase  MIS major’s strengths

6. Database = Tables+ Relationships + Metadata • Store data in a table format Table or Files

7. Database = Tables + Relationships+ Metadata Databases carry their data in the form of tables and represent relationships using foreign keys Patient How do we suppose to know which doctor will see which patient at 3pm on October 5th? Appointment Doctor

8. Database = Tables + Relationships + Metadata • Build the relationship among tables using Foreign Keys • Foreign Key: Primary key for one table which is passed to other table(s) to make relationships Patient How do we suppose to know who (doctor) will see whom (patient) at 3pm on October 5th? Primary Key Appointment Foreign Key Doctor Foreign Key Primary Key

9. Database = Tables + Relationships + Metadata • A table that describes the characteristics of data • Field Name (e.g., AppDate) • Data Type (e.g., Date/Time) • Description (e.g., Symptom) • Field Properties • Data Format (e.g., Short Date) • Input Mask (e.g., “99/99/1000”) • Default Value (e.g. = Now())

10. Database Management System (DBMS) • Database Management System (DBMS) • Application that creates, processes, and administrates database • e.g. ACCESS and SQL Server for Microsoft, DB2 for IBM, Oracle DB for Oracle, MySQL by open source  Will use MS ACCESS/SQL Server in this course • DBMS Functions • Create tables and relationships in databases • Process database • Read, insert, modify, delete data using SQL (Structured Query Language) INSERT INTO Student ([Student Number], [Student Name], HW1, HW2, MidTerm) VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100) • Provide tools to assist in administration of database • Setup passwords for Database files • Limit permissions to read/write data (e.g. employee’s salary data)

11. Topics Covered 4 Weeks 3 Weeks 3 Weeks 3 Weeks

12. Course Information • Gabe’s Contact: • E-mail: gabe.lee@uni.edu • Phone #: 319-273-6135 (Office) • Office Hours: 12:30-1:30 pm TTH or by appointment • Materials • Hoffer, J., M. Prescott and H. Topi, Modern Database Management, 10th Edition, Pearson Prentice-Hall, 2011 (Required). • Handouts/Reading Materials: Copies will be available on the course website. • MS Access book of your choice (Optional but highly recommended) or http://functionx.com/access/index.htm • MS SQL Server: http://functionx.com/sqlserver • Course Website: http://business.uni.edu/iss/mgmt3120

13. Course Info. • Required Software • MS VISIO and MS ACCESS 2010. • Both these software packages are installed in the UNI B-school labs (the 2010 version). • Software is available to download via the MSDNAA alliance at UNI.  contact me if you do not have a MSDNAA account • MS SQL Server 2012 Express Edition will be installed into CBA 224 • Can download it from Microsoft website http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

14. Course Requirements

15. Course Requirements • Unit Exams • Three unit exams (each 150 pts) from lecture notes, textbook and reading materials. • A mix of questions, including short answer questions, diagram drawing, database query and multiple choice questions. The actual format of the exam will be discussed in class prior to each exam. • Database Project (Team Project) • Develop a small-scale database application (High Fashion Inc.) • Team: 3 or 4 members • Require to submit 3 deliverables (see the detailed schedule in the course schedule table). • Deliverable #1 : Data Model (E-R Diagram) • Deliverable #2 : Relational Model and Data Dictionary • Deliverable #3 : Database Prototype • Will hand out detailed guidelines.

16. Course Requirements • HOMEWORK (Individual) • Complete two individual homeworks • Data Modeling Homework (AMCIS case) • SQL Homework (Virtual Tune case) • Will hand the homework out shortly • It is an individual homework, not a team project, so you should do it individually. • In-class exercises & quizzes • Expect several in-class individual & group exercises & quizzes (announced and unannounced) during the semester

17. Instructor Expectations • Students are expected to read the assigned textbook chapters PRIOR to the lecture period. • Lectures are designed to help students through the more difficult sections of the textand to integrate the materials, not to deliver summarized text. • Assignments should be turned in “BEFOREdue date & time”. • Late penalty will be applied if you submit assignments late: • Within 24 hours of the due date & time: A maximum of 80% of the total point. • After 24 hours of the due date & time: No points will be awarded • Exception will only be granted for valid & documentedreasons (should contact your instructor prior to the due date to get permission)

18. Instructor Expectations • Attendance willbe taken in this class. • Students who participate in lectures and work on lab exercises performed better than those who don’t  critical for your success • 5 points penalty will be granted for every unexcused absence, chronic tardiness and early leave (without excuse) • One unexcused absence, chronic tardiness, and early leave will be allowed without penalty. • NOT be given an opportunity to make up quizzes and hands-on lab exercises for any unexcused absence.

19. Instructor Expectations • Texting,Emailing, surfing the Web, or playing games are notpermitted during class. All electronic devices (e.g., MP3 players, cell phones, notebook) should be turned off and kept in your book bag during the class session.

20. CBA Cheating Policy • EXAM Any student who cheats during an examination or who assists another student in cheating during an examination will automatically FAIL the course. Cheating includes, but is not limited to: crib sheets, copying answers from another student's exam, use of recording devices and cellphone, and gaining unauthorized prior access to exams or answers. • ASSIGNMENTS Any student who cheats on an assignment; or any student who assists another student to cheat on an assignment will be subject to a penalty determined at the instructor’s discretion. Cheating includes, but is not limited to: submitting any other person's work as your own (or any portion thereof) or similar offenses. If you have any doubt, ask your instructor before you submit the work as your own. • See the Syllabus for details.

21. UNI Disability Policy • Affirmative Action/Disability Policy • The College of Business Administration at the University of Northern Iowa seeks to provide equal access to its programs, services, and activities for people with disabilities. If you will need accommodations in this class, reasonable prior notice needs to be given to the professor and the Student Disability Services Office (273-2677, SHC 103) to make arrangements for accommodations.

22. Your Instructor: Gabe Lee What program does God use to upload the sun every morning?

23. Next Class… • Bring Textbook • Read Chap 1 ( pp. 1 – 24) • Fill out Seating Chart • Required to sit in the same seat during the whole semester • Come to next class early and find your favorableseat. • Take pictures (Bring Name Tent) • Project Team Creation • Any Questions?

24. Database = Tables + Relationships + Metadata Build the relationship among tables using Foreign Keys • Primary Key (or Identifier): afield in a table that identifies each row uniquely (e.g. SSN for customer table, Doctor’s ID for doctor table, Student ID for student record table) Patient Primary Key