Database Concepts

Catalog Description:

A general introduction to the Database concepts. Historical development, concepts and definitions, data models, database analysis, design and implementation, query languages, data security, and the introduction to business transaction systems. Design concepts and procedures, as well as practical aspects of design, normalization up to third normal form and higher levels of normalization, entity-relationship diagrams, and object-oriented diagrams will be discussed. This course introduces the database design and development using modern DBMS

Course Objectives

Upon successful completion of this course, a student should be able to:

• Describe database systems and concepts.
• Explain modern database terminology and types of database models.
• Use of databases in industry.
• Discuss the database development process and business rules.
• apply advanced techniques of data modeling.
• design databases, create entity-relationship models, and semantic-object models.
• work with different DBMS, such as ORACLE and/or MS Access.
• use different tools for retrieval of data, such as Structured Query Language (SQL) and/or QBL (Query by Example)
• Write SQL statements for data manipulations using single or multiple tables.
• create tables, queries, and reports.

Group Project (My work)

Entity-Relationship Diagram (Final)

3 Questions

List which employees (emp_name) are responsible for each customer (cust_id, cust_name) along with the customer contact person (cust_contact) and the epmployee's job title (emp_title)

SELECT cust_id, cust_name, cust_contact, emp_name, emp_title
FROM CUSTOMER join EMPLOYEE using (emp_num);



List the current projects by id and name, the employees and their job titles working on the project, the number of hours worked on the project, and the skill used for the project.

SELECT
    ep.proj_num,  p.proj_name, e.emp_name, e.emp_title, ep.emp_projHrs, s.skill_name
FROM
    employee_project ep
    JOIN employee e ON ep.emp_num = e.emp_num
    JOIN project p ON ep.proj_num = p.proj_num
    LEFT JOIN employee_skill es ON ep.emp_num = es.emp_num
    LEFT JOIN skill s ON es.skill_num = s.skill_num;



List the competitor's products and inventions alongside the invention name and creator. Include the product name, cost, MSRP, and ranking. Make sure to sort by product cost in descending order.

select c.comp_name, c.comp_products, c.comp_invent, i.invent_name, i.invent_creator, p.prod_name, p.prod_cost, p.prod_msrp, p.prod_rank
    from competitor c
    left join invention i on c.comp_id = i.comp_id
    left join product p on c.comp_id = p.comp_id
    order by p.prod_cost desc;

Discussion boards