MySQL IGoalIntroduce the basic concepts of the database management system and MySQL. Start MySQL instance on Amazon RDS. Create tables in MySQL with Java.
Database and Database Management System
What is a Database?
A database is an organized collection of data.
What is a Database Management System?
A database management system (DBMS) is a computer-software application that interacts with end-users, other applications, and the database itself to capture and analyze data. A general-purpose DBMS allows the definition, creation, querying, update, and administration of databases. Why do we need a Database? We need to store some data set, a list of jobs with id, name, address, and date. What will you do? Text File? Excel? The size of the list is large( > 1 million users). Add some constraints to some data, such as the ID of each user should be different. Create relations between different kinds of data, such as users saved some jobs before. Quickly retrieve data based on a given condition, such as retrieve all jobs in San Francisco. Quickly update or delete data based on given conditions, such as updating all favorite jobs for a given user. Need access control on the data, meaning only authorized users can have access to the data set. Allow multiple users access(add, search, update, delete) the data set at the same time.
A DBMS allows you to fulfill all requirements above easily.
MySQLMySQL is an open-source relational database management system (RDBMS). Basic Concepts Table: a collection of attributions. Similar to what you’ve seen in an excel chart. Each column is an attribute of an entity, and each row is a record/instance of an entity. Row: a single, implicitly structured data item in a table Column: a set of data values of a particularly simple type, one for each row of the table Schema: the blueprint of how the table is constructed. SQL: a programming language that is used to communicate with the DBMS.
Tables for Jupiter project
Entity Relation Diagram Entities, which are represented by rectangles. An entity is an object or concept about which you want to store information. Actions, which are represented by diamond shapes, show how two entities share information in the database. Attributes, which are represented by ovals. A key attribute is the unique, distinguishing characteristic of the entity. For example, an employee's social security number might be the employee's key attribute. users - store user information.
items - store item information. | | | | | | Software engineer at laioffer | | | | | Data engineer at laioffer | | | | | | | | |
keyword - store item-keyword relationship It’s an implementation detail, we could save keywords in the item table, but there will be more string join/split manipulations in our code, so let’s save them in a separate table.
Primary key = item_id + keyword Foreign key = item_id => items(item_id) history - store user favorite history Primary key = item_id + user_id Foreign key = user_id => users(user_id) Foreign key = item_id => items(item_id)
A few more concept Primary key: Also a key that is unique for each record. It cannot be NULL and used as a unique identifier. Foreign key: a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
Setup MySQL on Amazon RDSAmazon Web Service(AWS) IntroductionService Models
| 
京公网安备11010202008513号 京ICP证101109号 京ICP备12012021号
发表于 2019-10-20 07:58:51
收藏
收藏
楼主
